I have the following data in SQL 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 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

>Solution :

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