How to Calculate Mode on Grouped Data

[loyola9988]

Hi,
I have a database which includes A column for Policy ID and a column for Payments. e.g.:

ID Payment
8006 400
8006 300
8006 400
8007 200
8007 100
8007 100


I need to find the mode on Payment column which is the most common amount for each policy. so end result would be
ID Payment
8006 400
8007 100


here is my query:
SELECT Count(*) AS RANK, AS400_Payments.ID, AS400_Payments.Amount AS Amount
FROM AS400_Payments
GROUP BY AS400_Payments.ID, AS400_Payments.Amount
ORDER BY Count(*) DESC;


but this show all payments with their frequencies not the most common one.
Please help!
Thanks very much in advance

 

[plog]

Using your query, build another query:

SELECT MAX(RANK) AS PaymentMode, ID FROM YourQuery GROUP BY ID

Let's call that MyQuery. It determines the mode for each ID. Now, build another query using YourQuery and MyQuery:

SELECT YourQuery.ID, YourQuery.Amount
FROM YourQuery
INNER JOIN MyQuery ON YourQuery.ID = MyQuery.ID AND RANK =PaymentMode

It shows the payment amount modes of each account. Here's what it doesn't do--show the individual payments, nor break ties. If an ID has 3 payments of 300 and 3 payments of 200 and all other payment values occur less than 3 times, then both those payment values will show for the ID.

 

[loyola9988]

Thanks for the reply. I tried it and I am getting the following error message:
"JOIN expression not supported."

 

[loyola9988]

It worked!
I just noticed it was missing brackets:
SELECT YourQuery.ID, YourQuery.Amount
FROM YourQuery
INNER JOIN MyQuery ON (YourQuery.ID = MyQuery.ID AND RANK =PaymentMode)


Thanks for your help