How to sort and limit an SQL table by sum of a numerical column grouped by multiple categories?-CodePudding

I have the following SQL table:

Country	ProductID	Price
US	1	10
US	2	20
GB	3	25
GB	4	40
US	1	10
US	1	10
DE	2	20
DE	2	20

I want to see the two most profitable (per sum of price) "ProductID" per "Country" (sorted by Sum per "Country") like this:

Country	ProductID	Sum
US	4	40
US	1	30
GB	4	40
GB	3	25
DE	2	20

How can I perform this?

CodePudding user response：

We can use RANK here in conjunction with an aggregation by country and product:

WITH cte AS (
    SELECT Country, ProductID, SUM(Price) AS Sum,
           RANK() OVER (PARTITION BY Country ORDER BY SUM(Price) DESC) rnk
    FROM yourTable
    GROUP BY Country, ProductID
)

SELECT Country, ProductID, Sum
FROM cte
WHERE rnk <= 2
ORDER BY Country, rnk;