SQL select the max value of a sum

Question

I'm new to the forum and SQL and have really appreciated all the information. I couldn't find a solution that works for me already addressed so thought I'd give this a shot here. Operating in Snowflake. My dataset looks like this (apologies for troublesome formatting):

PO  | DIV | PROD |  QTY |   CUST
123 | 1  | x |  10 |    Sonic
234 |   1 | x   | 9 |   Sonic
345 | 1 |   x | 8   | McD
456 | 1 | x | 10    | Wendy's

I would like to sum on QTY by DIV, PROD, and CUST. Once I have those summed up quantities, I would like to take the largest SUM(QTY) by DIV and PROD but retain the CUST field. Thus the answer from the above would look like this:

1 | x | 19| Sonic

Just note, I obviously have a much larger database so that i would have all the unique product/division combinations with the max(sum()) listed, about 600K rows.

The code I have here gets me to the point where I have the sum(QTY) but now I need to pull the row with the max sum(QTY) while retaining the CUST field. Can you help? I found some info on greatest-n-per-group but wasn't sure if that's what I should be using and how

SELECT DIV, PROD, CUST, SUM(QTY) as QTY
from table
GROUP BY 
    DIV,
    PROD,
    CUST
Order by 
    DIV,
    PROD

EDIT: Jay's CTE approach worked for me but I forgot to mention that I'd like to create a table out of this. CREATE OR REPLACE TABLE didn't work with the CTE. Any ways to create a table using the CTE approach, or a different approach?

Thanks!

Answer 1

MYSQL

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T
WHERE QTY=(SELECT MAX(QTY) FROM (SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD) AS T)

OR

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T ORDER BY QTY DESC LIMIT 1;

Live Demo

http://sqlfiddle.com/#!9/f945c2b/19

SQL SERVER

Using CTE

WITH CTE AS
(
 SELECT DIV, PROD, CUST, SUM(QTY) as QTY,
 DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
 FROM 
 table1 
 GROUP BY DIV, PROD, CUST
)
SELECT DIV, PROD, CUST,QTY FROM CTE
WHERE Rank=1
ORDER BY DIV, PROD

Using Nested Query

SELECT DIV, 
       PROD, 
       CUST,
       QTY 
FROM ( 
      SELECT DIV, PROD, CUST, SUM(QTY) as QTY, 
      DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
      FROM table1 
      GROUP BY DIV, PROD, CUST )AS T1 
 WHERE Rank=1 ORDER BY DIV, PROD;

Live Demo

http://sqlfiddle.com/#!18/22001/11