Challenging PostgreSQL SELECT Statement for Northwind Database (NEED HELP - BEGINNER)

Question

I began learning SQL about a month ago and my dad has been giving me practice queries to run with the Northwind database to help practice my DML. This most recent one he gave me was as follows: -- Return Month, Product name, SalesForMonth for highest selling product in each -- month in 1997. (see issues with creating said query below)

(I am currently using PostgreSQL on pgAdmin4)

I was able to come up with the following query that returns the required columns with the correct information for ONLY a single month:

SELECT CAST( EXTRACT( MONTH FROM o.orderdate) AS integer) AS Month, p.productname, 
    ROUND(CAST(SUM(od.quantity * od.unitprice) AS numeric), 2) SalesForMonth
FROM order_details od 
INNER JOIN orders o ON od.orderid = o.orderid
INNER JOIN products p ON od.productid = p.productid
WHERE EXTRACT( YEAR FROM o.orderdate) = 1997 AND EXTRACT( MONTH FROM o.orderdate) = 1
GROUP BY Month, p.productname
ORDER BY salesformonth DESC
LIMIT 1

By making 12 of these queries and changing the extract-month bit in the WHERE statement from 1-12 and UNIONing them all together, I can produce the desired result but I wondered if there was an easier way that I was missing to display the same result but only using 1 query. Interested to see what y'all can come up with.

SIDE NOTE: My initial thought is that it has something to do with subqueries because what you're effectively trying to do is display the MAX(SUM(values)) but can't actually do that since you can't nest aggregate function.

Answer 1

Your query gives you the top selling product for a given month, and you want the same logic for multipe months at once.

Starting from your existing and working query, a simple approach is to use WITH TIES:

SELECT DATE_TRUNC('month', o.orderdate) DateMonth, 
    p.productname, 
    ROUND(CAST(SUM(od.quantity * od.unitprice) AS numeric), 2) SalesForMonth
FROM order_details od 
INNER JOIN orders o ON od.orderid = o.orderid
INNER JOIN products p ON od.productid = p.productid
WHERE o.orderdate >= DATE '1997-01-01' AND o.orderdate < DATE '1998-01-01'  
GROUP BY DateMonth, p.productname
ORDER BY RANK() OVER(
    PARTITION BY DATE_TRUNC('month', o.orderdate) 
    ORDER BY SUM(od.quantity * od.unitprice) DESC
)
FETCH FIRST ROW WITH TIES 

We can also use DISTINCT ON:

SELECT DISTINCT ON (DateMonth)
    DATE_TRUNC('month', o.orderdate) DateMonth, 
    p.productname, 
    ROUND(CAST(SUM(od.quantity * od.unitprice) AS numeric), 2) SalesForMonth
FROM order_details od 
INNER JOIN orders o ON od.orderid = o.orderid
INNER JOIN products p ON od.productid = p.productid
WHERE o.orderdate >= DATE '1997-01-01' AND o.orderdate < DATE '1998-01-01'  
GROUP BY DateMonth, p.productname    
ORDER BY DateMonth, SalesForMonth DESC