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.