Sum and count of grouped records-CodePudding

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:

A count of same values of Col2 with correspondig Col1 and SUM of Col3.
A sum and count of grouped results.

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

Demo

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

CodePudding user response：

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.