SQL, get the highest payment month of each year

Question

I currently have this that shows me the total of how much the amount is per month. What i want to get from this, is to show the month that made the highest amount per each year.

SELECT year(paymentDate), month(paymentDate) , SUM(amount) 
FROM classicmodels.payments
GROUP BY year(paymentDate), month(paymentDate)
ORDER BY paymentDate ASC;

Here is the table that i want to only show the month that got the highest amount for each year

score 0 · Answer 1 · edited Oct 24 '17 at 09:09

0

Give SUM(Amount) a column alias, ORDER BY <that alias> DESC and add TOP(1) to the SELECT

edited Oct 24 '17 at 09:09

Abdul Rasheed

6,486
4
32
48

answered Sep 28 '17 at 05:28

SQLBadPanda

625
5
7

score 0 · Answer 2 · answered Sep 28 '17 at 05:36

0

You can use ROW_NUMBER for this:

SELECT y, m, amount
FROM (
   SELECT YEAR(paymentDate) AS y, MONTH(paymentDate) AS m, 
          amount,
          ROW_NUMBER() OVER (PARTITION BY YEAR(paymentDate) ORDER BY amount DESC) AS rn
FROM classicmodels.payments) AS t
WHERE t.rn = 1

Note: In case of ties the query return an arbitrary month. To return all months having the biggest amount use RANK instead of ROW_NUMBER.

answered Sep 28 '17 at 05:36

Giorgos Betsos

71,379
9
63
98

Because this is in sql-server and the OVER clause will not work on mysql workbench. – Nagne Sep 29 '17 at 18:47

Abdul Rasheed · Answer 3 · 2017-09-28T05:51:02.297

0

Using ROW_NUMBER() you can assign sequence number for each records based on your criteria, and select the record with sequence 1. Sample below;

SELECT  *
FROM    (
    SELECT year(paymentDate) year_val, month(paymentDate) month_val, SUM(amount) amt_val,
            ROW_NUMBER() OVER(PARTITION BY year(paymentDate) ORDER BY SUM(amount) DESC) AS ROW_ORDER
    FROM classicmodels.payments
    GROUP BY year(paymentDate), month(paymentDate)
)   AS  D
WHERE   D.ROW_ORDER = 1

There are lot of other ways to achieve the same.

and WHERE D.ROW_ORDER = n --you will return the n'th highest for each year.

edited Sep 28 '17 at 05:51

answered Sep 28 '17 at 05:37

Abdul Rasheed

6,486
4
32
48

Because this is in sql-server and the OVER clause will not work on mysql workbench. – Nagne Sep 29 '17 at 18:47
when i post this answer, the sql-server tag was there. Sorry i don't have experience in MySql. But you can try some think similar to ROW_NUMBER, eg:- https://stackoverflow.com/questions/19589707/how-to-get-rownumber-with-partition-in-mysql – Abdul Rasheed Oct 24 '17 at 09:06

SQL, get the highest payment month of each year

3 Answers3