CODEWITHSUNDEEP
sql
Bob F
Bob F

Reputation: 39

subquery to find the max of a sum

I have a sales table that contains

date, store, weekly sales

I want to find the store with the highest amount of sales per year. For example

year     store    total sales
2010      4        $2,000,000
2011      25       $1,000,000

My sub query works but I can't find the max of it

SELECT year, store, MAX(total_sales)
FROM (SELECT
     date_part('year', date) AS year, store, SUM(weekly_sales)
     FROM sales
     GROUP BY year, store) AS total_sales
GROUP BY year, store;

Upvotes: 0

Views: 17

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Use row_number():

SELECT year, store, total_sales
FROM (SELECT date_part('year', date) AS year, store, SUM(weekly_sales) as total_sales,
             ROW_NUMBER() OVER (PARTITION BY date_part('year', date) ORDER BY SUM(weekly_sales) DESC) as seqnum
       FROM sales
       GROUP BY year, store
     ) ys
WHERE seqnum = 1;

Upvotes: 1

Related Questions