SQL Query to group data without using cursor

Question

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.

Your sample data doesn't seem to match your expected results, you seem to have the rows off by one. — Charlieface
– Charlieface, Commented Jul 30, 2024 at 19:17

Charlieface · Accepted Answer · 2024-07-30 19:17:59Z

4

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

answered Jul 30, 2024 at 19:17

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

developer Over a year ago

thanks for the answer, learnt something new :)

bsraskr · Accepted Answer · 2024-07-30 19:31:33Z

4

You can use over partition by statement like this,


select Category,Date,
(SUM(Price) * 100.0) / SUM(SUM(Price)) OVER (PARTITION BY Date) AS Price
from your_table
group by Category,Date 
order by Category,Date;

edited Jul 30, 2024 at 19:31

answered Jul 30, 2024 at 19:15

bsraskr

7237 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL Query to group data without using cursor

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related