How to use SUM and MAX on the same column?

So I’m having an SQL table where I will need find out the product that has been purchased the most meaning that I need to do a SUM and a group by on all the quantity of the products:

SELECT PRODUCT_ID, SUM(QUANTITY) FROM PURCHASE GROUP BY PRODUCT_ID

However when I try to find the product with the maximum amount of purchases it gives me an error:

SELECT MAX(QUANTITY) FROM(SELECT PRODUCT_ID, SUM(QUANTITY) FROM PURCHASE GROUP BY PRODUCT_ID)

Any ideas?

Answer

Just order by and keep the top record only:

SELECT PRODUCT_ID, SUM(QUANTITY) SUM_QUANTITY
FROM PURCHASE 
GROUP BY PRODUCT_ID 
ORDER BY SUM_QUANTITY DESC
LIMIT 1

The actual syntax might vary accross RDBMS. The above would work in MySQL and Postgres.

In SQL Server, you would use SELECT TOP (1) ... ORDER BY SUM_QUANTITY DESC.

In Oracle >= 12c, you would use SELECT ... ORDER BY SUM_QUANTITY DESC FETCH FIRST ROW ONLY.

You also have to consider the possibilty of ties in the first position, for which there are different strategies depending on your requirement and RDBMS.