CODEWITHSUNDEEP
mysqlsql
Nagne
Nagne

Reputation: 11

MySQL Finding the MAX of the SUM of each month of the year [MYSQL]

Currently i am able to get the sum for the highest amount for each month of the year. But what i want to do, is to be able to get the SUM of the month that has the highest value in amount for each year. 

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

This orders the highest SUM(amount) in descending order but i only want to get the highest month for each year. there are only 3 years in my database.

Here's what happening on mysql workbench

Upvotes: 0

Views: 293

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

One method uses a having clause:

SELECT year(p.paymentDate), month(p.paymentDate), SUM(p.amount)
FROM classicmodels.payments p
GROUP BY year(p.paymentDate), month(p.paymentDate) 
HAVING SUM(p.amount) = (SELECT SUM(p2.amount)
                        FROM classicmodels.payments p2
                        WHERE year(p2.paymentDate) = year(p.paymentDate)
                        GROUP BY month(p2.paymentDate)
                        ORDER BY SUM(p2.amount) DESC
                        LIMIT 1
                       )
ORDER BY SUM(amount) DESC;

Upvotes: 1

Related Questions