Frequency of payments

[FrankW]

I am trying to find the most frequent payment amount in a sql table spanning a 12 month period for 300,000 individual accounts. Does anyone know how I would go about writing a query in SQL to return the most frequent payment and the number of times it occurs throughout the 12 month period. Each month is represented in a column such as:

Month1 Month2 month3 month4 Month5 Month6 etc etc..
120 110 120 90 120 100 ....

For this example I would like to return 120 and the count of how many times it occurs...

Is there any type of function that will help with this or does anyone know the easiest way to write a query to return this?

Thanks,

FrankW

 

[rac2]

And in the real data are there payment amounts that differ by pennies, 110.51 and 110.52? If so, you are going to get a huge number of categories in your result, like maybe 99,999 different payment amounts.

That said, here is one way to get the answer.

First make views that re-arrange the payments into one column. At the same time reduce the data by getting counts for each month.

CREATE VIEW PaymentFrequencyMonth1
AS
SELECT Month1 AS "Payment", COUNT(Month1) AS "Frequency"
FROM Payments
GROUP BY Month1

CREATE VIEW PaymentFrequencyMonth2
AS
SELECT Month2 AS "Payment", COUNT(Month2) AS "Frequency"
FROM Payments
GROUP BY Month2

etc.

Then put these together with UNION ALL

CREATE VIEW PaymentFrequency
AS

SELECT Payment, Frequency
FROM PaymentFrequencyMonth1

UNION ALL

SELECT Payment, Frequency
FROM PaymentFrequencyMonth2

UNION ALL

SELECT Payment, Frequency
FROM PaymentFrequencyMonth3

etc.

Then get the answer you seek.

SELECT Payment, SUM(Frequency)
FROM PaymentFrequency
GROUP BY Payment
ORDER BY SUM(Frequency)

There might be some restrictions on creating views with UNION ALL in which case you can create a temporary table instead.

I haven't tried this, but it seems like it might work.