[SOLVED] SQL Query to group data without using cursor

SQL Query to group data without using cursor

I have the following table:

Category	Product	Date	Price
C1	P1	01/01/2024	1
C1	P2	01/01/2024	2
C1	P3	01/01/2024	3
C1	P1	01/02/2024	4
C1	P2	01/02/2024	5
C1	P3	01/02/2024	6
C1	P1	01/03/2024	7
C1	P2	01/03/2024	8
C1	P3	01/03/2024	9
C2	P4	01/01/2024	10
C2	P5	01/01/2024	11
C2	P6	01/01/2024	12
C2	P4	01/02/2024	13
C2	P5	01/02/2024	14
C2	P6	01/02/2024	15
C2	P4	01/03/2024	16
C2	P5	01/03/2024	17
C2	P6	01/03/2024	18

I would like to create a summary of this table as per the following format:

Category	Date	Price
C1	01/01/2024	15.38 [formula (1+2+3) * 100/Sum(1+2+3+10+11+12)... i.e. sum of C1 category's data where date = 01/01/2024 .. divided by sum of all category's data where date = 01/01/2024]
C1	01/02/2024	25 [formula (4+5+6) * 100/Sum(4+5+6+14+15+16)... i.e. sum of C1 category's data where date = 01/02/2024 .. divided by sum of all category's data where date = 01/02/2024]

I can do this using a cursor but wondering if it is possible to do this using a SQL query using group by clause.

Solution

You can use normal aggregation, and then use a window function on top of that

SELECT *
FROM (
    SELECT
      t.Category,
      t.Date,
      SUM(t.Price) AS TotalPrice,
      SUM(SUM(t.Price)) OVER (PARTITION BY t.Date) AS TotalForAll,
      SUM(t.Price) * 100 / SUM(SUM(t.Price)) OVER (PARTITION BY t.Date) AS [Percent]
    FROM YourTable t
    GROUP BY
      t.Category,
      t.Date
) t
WHERE t.Category = 'C1';

db<>fiddle