sqlitedelphi-10.2-tokyo

Sum and count of grouped records


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:

  1. A count of same values of Col2 with correspondig Col1 and SUM of Col3.
  2. 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

Solution

  • 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.