Lets say i have a table:
| Col1 | Col2 | Col3 |
|---|---|---|
| R1 | C1 | 5 |
| R2 | C3 | 8 |
| R1 | C1 | 2 |
| R1 | C2 | 4 |
| R2 | C5 | 3 |
| R2 | C2 | 4 |
I need to get:
Col2 with correspondig Col1 and SUM
of Col3.To achive the #1 my code looks like that:
SELECT Col1, Col2, COUNT(*), SUM(Col3)
FROM myTable
GROUP BY Col1, Col2
I get the result (and it is ok):
| Col1 | Col2 | Count | Sum |
|---|---|---|---|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
For #2 i need to know the SUMof values of column Count and the SUM of values of column SUM, where values of column Col1 are equal. How could i upgrade my code?
The desired result would be something like that:
| Col1 | Col2 | Count | Sum |
|---|---|---|---|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| 3 | 11 | ||
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| 3 | 15 |
You can simulate rollup records by adding records, that aggregate only to "Col1" values, to your initial result set, using a UNION ALL operation.
SELECT Col1, Col2, COUNT(*) AS cnt, SUM(Col3) AS total FROM myTable GROUP BY Col1, Col2
UNION ALL
SELECT Col1, NULL, COUNT(*) , SUM(Col3) FROM myTable GROUP BY Col1
ORDER BY Col1
Output:
| Col1 | Col2 | cnt | total |
|---|---|---|---|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| R1 | null | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| R2 | null | 3 | 15 |
Check the demo here.