Search code examples
sqlt-sqldatabase-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