SQL query for finding maximum amount month wise with the user

Question

I want to print maximum sum amount per month with the user who has done the maximum amount. My table structure is

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| customername | varchar(20) | YES  |     | NULL    |       |
| processed_at | date        | YES  |     | NULL    |       |
| amount       | int(11)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

and the query to print maximum sum month wise is

SELECT MAX(A.AMT), A.month
FROM ( SELECT customername,SUM(amount) AS AMT, EXTRACT( month from processed_at) as month
FROM payments
GROUP BY customername,month) AS A
GROUP BY  2;

this is giving the result

| MAX(A.AMT) | month |
+------------+-------+
|       1900 |     4 |
|       2400 |     3 |

is there a way in which customername can also be mapped to maximum amount?

You have mistakenly tagged two different DBMS: Oracle and MySQL. Which of the two are you using and which version exactly? — Thorsten Kettner, Apr 14 '20 at 07:22
Either way, this is the most frequently asked question under these tags. — Strawberry, Apr 14 '20 at 07:22
You want to display the maximum amount per month and per customer or just per month? Anyway you could use a subquery to display max customer value from that month. — Uxio90, Apr 14 '20 at 07:29
I want to display maximum amount per month and using mysql 8.0.18 — Aakash Wadhwa, Apr 14 '20 at 07:49

GMB · Accepted Answer · 2020-04-14T09:07:38.963

If you are running MySQL 8.0, you can use window functions for this:

SELECT *
FROM (
    SELECT 
        customername,
        SUM(amount) amt, 
        DATE_FORMAT(processed_at, '%Y-%m-01') yr_month,
        RANK() OVER(PARTITION BY DATE_FORMAT(processed_at, '%Y-%m-01') ORDER BY SUM(amount) DESC) rn
    FROM payments
    GROUP BY customername, yr_month
) t
WHERE rn = 1

Note that this includes the month and year in the GROUP BY clause, in case your data spreads over more than 12 months.

In earlier versions, one option is to filter with a HAVING clause and a correlated aggregate query:

SELECT 
    customername,
    SUM(amount) amt, 
    DATE_FORMAT(processed_at, '%Y-%m-01') yr_month
FROM payments p
GROUP BY customername, yr_month
HAVING COUNT(*) = (
    SELECT SUM(p1.amount)
    FROM payments p
    WHERE DATE_FORMAT(p1.processed_at, '%Y-%m-01') = yr_month
    GROUP BY p1.customername
    ORDER BY SUM(p1.amount) DESC
    LIMIT 1
)

SQL query for finding maximum amount month wise with the user

1 Answers1