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 SUM
of 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.