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;
Advertisement
Answer
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;