Search code examples
sqlranking

Sum of Top N in SQL


I have a SALES table with Person, Date and Qty:

Person   Date      Qty
Jim     2016-08-01  1
Jim     2016-08-02  3
Jim     2016-08-03  2
Sheila  2016-08-01  1
Sheila  2016-08-02  1
Sheila  2016-08-03  1
Bob     2016-08-03  6
Bob     2016-08-02  2
Bob     2016-08-01  5

I can rank the top 2 by Date with the following code:

/****** Top 2 Salespersons  ******/
SELECT * 
FROM(
    SELECT * ,
    ROW_NUMBER() OVER( PARTITION BY [Date] 
    ORDER BY Qty DESC) N'Rank' 
    FROM [Coinmarketcap].[dbo].[sales]
    GROUP BY [Date], Person, Qty
    ) AS NewTable
WHERE NewTable.Rank  < 3

Person     Date    Qty Rank
Bob     2016-08-01  5   1
Jim     2016-08-01  1   2
Jim     2016-08-02  3   1
Bob     2016-08-02  2   2
Bob     2016-08-03  6   1
Jim     2016-08-03  2   2

My two questions are:

1) How can I just see the total qty for the top 2 for each date, such as:

Date       Total Qty
2016-08-01    6
2016-08-02    5
2016-08-03    8

2) How can I get the total Qty each day for different ranking groups, such as:

Date         Ranking Group    Total Qty
2018-08-01     1-2               6 
2018-08-01     3-4               1 
2018-08-01     5-6               0
2018-08-02     1-2               5 
2018-08-02     3-4               1 
2018-08-02     5-6               0
2018-08-03     1-2               8 
2018-08-03     3-4               1 
2018-08-03     5-6               0

Solution

  • First:

    SELECT NewTable.Date, Sum(NewTable.Qty) 
    FROM(
        SELECT * ,
        ROW_NUMBER() OVER( PARTITION BY [Date] 
        ORDER BY Qty DESC) N'Rank' 
        FROM [Coinmarketcap].[dbo].[sales]
        GROUP BY [Date], Person, Qty
        ) AS NewTable
    WHERE NewTable.Rank  < 3
    group by NewTable.Date
    

    Second try this:

    SELECT NewTable.Date, 
           Trunc((NewTable.Rank - 1) / 2) * 2 + 1, -- lower rank
           Trunc((NewTable.Rank - 1) / 2) * 2 + 2, -- upper rank
           Sum(NewTable.Qty) 
    FROM(
        SELECT * ,
        ROW_NUMBER() OVER( PARTITION BY [Date] 
        ORDER BY Qty DESC) N'Rank' 
        FROM [Coinmarketcap].[dbo].[sales]
        GROUP BY [Date], Person, Qty
        ) AS NewTable
    group by NewTable.Date, 
             Trunc((NewTable.Rank - 1) / 2) * 2 + 1,
             Trunc((NewTable.Rank - 1) / 2) * 2 + 2