How to select top items ordered by SUM for each month in the time range?-CodePudding

At the moment, my query looks like this:

SELECT TOP 1000 
    Yr, Mnth, Product, SUM(Price * Amount)
FROM
    (SELECT 
         Random_product_name AS Product, Random_price AS Price, 
         Random_quantity AS Amount,
         Random_order_dt AS Order_date, 
         YEAR(Random_order_dt) AS Yr, MONTH(Random_order_dt) AS Mnth
     FROM 
         Random_table_1 a
     JOIN 
         Random_table_2 b ON a.Random_ID = b.Random_ID
     WHERE 
         a.Random_product_name LIKE 'XX_%' 
         AND a.Random_country = 'XX'
         AND a.Random_price != 0 
         AND b.Random_order_dt BETWEEN 'Jan 01 2019 12:00AM' AND 'Jul 01 2021 12:00AM') x
GROUP BY 
    Yr, Mnth, Product
ORDER BY 
    Yr, CONVERT(INT, Mnth), CONVERT(INT, SUM(Price*Amount)) DESC

Don't pay attention to the detail, the real query looks a bit different, of course. But result of it might look like this:

Yr	Mnth	Product	SUM
2019	1	A	100 000
2019	1	B	90 000
2019	1	C	80 000
2019	1	D	70 000
2019	1	E	60 000
2019	1	F	50 000
2019	1	G	40 000
2019	2	B	120 000
2019	2	A	110 000
2019	2	D	90 000
2019	2	C	80 000
2019	2	E	60 000
2019	2	G	30 000
2019	2	F	20 000

And so on up until July 2021. Sum of products changes every month and what I would like to achieve is to display only top 3 of them for each month, but in one table, like this:

Yr	Mnth	Product	SUM
2019	1	A	100 000
2019	1	B	90 000
2019	1	C	80 000
2019	2	B	120 000
2019	2	A	110 000
2019	2	D	90 000

And so on. Again, up until July 2021.

The query is simplified. In reality it looks a bit more complicated, but I hope it is understandable enough what I want to accomplish. I don't know how to proceed, so I would appreciate any help you can provide! Thank you in advance!

CodePudding user response：

You can use the row_number with a partition_by clause with your select statement and then use the result in your where clause.

To add to the top-select :

 row_number() over(partition by Yr, Mnth order by SUM(Price * Amount) desc) as rownum

Then add a select on top of with the following where clause:

WHERE rownum <= 3

CodePudding user response：

I think ROW_NUMBER might help you

I couldn't test and i know it's not a pretty solution it but i think this is the general idea:

    SELECT * FROM  
        (SELECT 
            Yr, Mnth, Product, sold
            ROW_NUMBER() OVER (
                    PARTITION BY Yr, Mnth, Product 
                    ORDER BY sold DESC
            ) item_rank
        FROM
        (
            SELECT TOP 1000 
                Yr, Mnth, Product, SUM(Price * Amount) as sold
            FROM
                (SELECT 
                    Random_product_name AS Product, Random_price AS Price, 
                    Random_quantity AS Amount,
                    Random_order_dt AS Order_date, 
                    YEAR(Random_order_dt) AS Yr, MONTH(Random_order_dt) AS Mnth
                FROM 
                    Random_table_1 a
                JOIN 
                    Random_table_2 b ON a.Random_ID = b.Random_ID
                WHERE 
                    a.Random_product_name LIKE 'XX_%' 
                    AND a.Random_country = 'XX'
                    AND a.Random_price != 0 
                    AND b.Random_order_dt BETWEEN 'Jan 01 2019 12:00AM' AND 'Jul 01 2021 12:00AM') x
            GROUP BY 
                Yr, Mnth, Product
            ORDER BY 
                Yr, CONVERT(INT, Mnth), CONVERT(INT, SUM(Price*Amount)) DESC
        )
    )
    WHERE item_rank <= 3