I have the following data in a SQL Server table:
Guid Token Category DateCreated
-------------------------------------------
GUID1 TK1 Category1 1/9/2023 21:50
GUID2 TK2 Category1 1/10/2023 21:51
GUID3 TK3 Category1 1/11/2023 21:52
GUID4 TK4 Category1 1/12/2023 21:53
GUID5 TK5 Category1 1/13/2023 21:54
GUID6 TK6 Category1 1/14/2023 21:55
GUID7 TK7 Category1 1/15/2023 21:56
GUID8 TK8 Category1 1/16/2023 21:57
GUID9 TK9 Category1 1/17/2023 21:58
GUID10 TK10 Category1 1/18/2023 21:59
GUID11 TK11 Category1 1/19/2023 22:00
GUID12 TK12 Category2 1/20/2023 22:01
GUID13 TK13 Category2 1/21/2023 22:02
GUID14 TK14 Category2 1/22/2023 22:03
GUID15 TK15 Category2 1/23/2023 22:04
GUID16 TK16 Category3 1/24/2023 22:05
GUID17 TK17 Category3 1/25/2023 22:06
GUID18 TK18 Category3 1/26/2023 22:07
GUID19 TK19 Category3 1/27/2023 22:08
I wan to get the result as shown below (get the position of a token within its category and total tokens in that category):
Token Position# OutOftotal (Where Category ='Category1' and Token='TK8')
TK8 8 11
Token Position# OutOftotal (Where Category ='Category1' and Token='TK5')
TK5 5 11
Token Position# OutOftotal (Where Category ='Category3' and Token='TK17')
TK17 2 4
CodePudding user response：
Here is an option using the window function sum() over()
with cte as (
Select *
,[Position] = sum(1) over (partition by Category order by [DateCreated])
,[OutofTotal] = sum(1) over (partition by Category)
From YourTable
Where Category ='Category1' -- Optional but STRONGLY suggested
)
Select [Token]
,[Position]
,[OutofTotal]
From cte
Where Category ='Category1'
and Token='TK8'
Results
Token Position OutofTotal
TK8 8 11
CodePudding user response：
Data
CREATE TABLE mytable(
Guid VARCHAR(100) NOT NULL
,Token VARCHAR(100) NOT NULL
,Category VARCHAR(100) NOT NULL
,DateCreated DATETIME NOT NULL
);
INSERT INTO mytable
(Guid,Token,Category,DateCreated) VALUES
('GUID1','TK1','Category1','1/9/2023 21:50'),
('GUID2','TK2','Category1','1/10/2023 21:51'),
('GUID3','TK3','Category1','1/11/2023 21:52'),
('GUID4','TK4','Category1','1/12/2023 21:53'),
('GUID5','TK5','Category1','1/13/2023 21:54'),
('GUID6','TK6','Category1','1/14/2023 21:55'),
('GUID7','TK7','Category1','1/15/2023 21:56'),
('GUID8','TK8','Category1','1/16/2023 21:57'),
('GUID9','TK9','Category1','1/17/2023 21:58'),
('GUID10','TK10','Category1','1/18/2023 21:59'),
('GUID11','TK11','Category1','1/19/2023 22:00'),
('GUID12','TK12','Category2','1/20/2023 22:01'),
('GUID13','TK13','Category2','1/21/2023 22:02'),
('GUID14','TK14','Category2','1/22/2023 22:03'),
('GUID15','TK15','Category2','1/23/2023 22:04'),
('GUID16','TK16','Category3','1/24/2023 22:05'),
('GUID17','TK17','Category3','1/25/2023 22:06'),
('GUID18','TK18','Category3','1/26/2023 22:07'),
('GUID19','TK19','Category3','1/27/2023 22:08');
use window functions such as row_number and max with over and Subquery as follows
SELECT Token,
Position,
Max(Position)
OVER(
PARTITION by Category ) OutOftotal
FROM (select Token,
Category,
DateCreated,
row_number()
over (
PARTITION by Category
order by DateCreated ) Position
FROM mytable) M