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
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