How to sum a count in SQL

Question

I just started using PL/SQL Developer and I've tried to Google this issue for hours to no avail.

Here is my Query:

SELECT "STATION_CODE", "STATUS", COUNT(1) 
FROM "Table" "R"
WHERE "STATUS" IN ('LOOKUP CLEAR') 
AND
NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) 
AND
"CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY')  
AND 
TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') 
AND
"STATION_CODE" IN ('Code1','Code2','Code3','Code4','Code5','Code6','Code7') 
GROUP BY "STATION_CODE", "STATUS";

Result:

+----------------------------------------+
|                 Results                |
+----------------------------------------+
| Station_Code |    Status    | Count(1) |
+--------------+--------------+----------+
| Code1        | Filter Clear | 5        |
+--------------+--------------+----------+
| Code2        | Filter Clear | 2        |
+--------------+--------------+----------+
| Code3        | Filter Clear | 5        |
+--------------+--------------+----------+
| Code4        | Filter Clear | 10       |
+--------------+--------------+----------+
| Code5        | Filter Clear | 3        |
+--------------+--------------+----------+
| Code6        | Filter Clear | 8        |
+--------------+--------------+----------+
| Code7        | Filter Clear | 2        |
+--------------+--------------+----------+

What I'd like to have is a sum as the very last row of the count. Something along the lines of:

+--------------+--------------+----------+
| Sum          |              | 35       |
+--------------+--------------+----------+

I believe there is a 'having' function that needs to be used but I cant for the life of me figure it out.

I'm not sure if this is relevant but the database connection that I have is a read only view.

Thanks in advance for the help!

Answer 1

It's called GROUPING SETS

The simplest solution would be to use GROUPING SETS in your GROUP BY clause:

SELECT DECODE(GROUPING_ID("STATION_CODE"), 0, "STATION_CODE", 'Sum'), "STATUS", COUNT(1)
FROM "Table" "R"
WHERE "STATUS" IN ('LOOKUP CLEAR') 
AND NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) 
AND "CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY')  
AND TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') 
AND "STATION_CODE" IN ('Code1','Code2','Code3','Code4','Code5','Code6','Code7') 
GROUP BY GROUPING SETS (("STATION_CODE", "STATUS"), ());

This would yield

+----------------------------------------+
|                 Results                |
+----------------------------------------+
| Station_Code |    Status    | Count(1) |
+--------------+--------------+----------+
| Code1        | Filter Clear | 5        |
+--------------+--------------+----------+
| Code2        | Filter Clear | 2        |
+--------------+--------------+----------+
| Code3        | Filter Clear | 5        |
+--------------+--------------+----------+
| Code4        | Filter Clear | 10       |
+--------------+--------------+----------+
| Code5        | Filter Clear | 3        |
+--------------+--------------+----------+
| Code6        | Filter Clear | 8        |
+--------------+--------------+----------+
| Code7        | Filter Clear | 2        |
+--------------+--------------+----------+
| Sum          |              | 35       |
+--------------+--------------+----------+

Having the sum in every row

Alternatively, what you can do is use a window function (also called analytic function in Oracle) to aggregate an aggregate function. The following query will work:

SELECT "STATION_CODE", "STATUS", COUNT(1), SUM(COUNT(1)) OVER()
FROM "Table" "R"
WHERE "STATUS" IN ('LOOKUP CLEAR') 
AND NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) 
AND "CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY')  
AND TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') 
AND "STATION_CODE" IN ('Code1','Code2','Code3','Code4','Code5','Code6','Code7') 
GROUP BY "STATION_CODE", "STATUS";

And it will yield:

+---------------------------------------------------------------+
|                 Results                                       |
+---------------------------------------------------------------+
| Station_Code |    Status    | Count(1) | Sum(Count(1)) Over() |
+--------------+--------------+----------+----------------------+
| Code1        | Filter Clear | 5        | 35                   |
+--------------+--------------+----------+----------------------+
| Code2        | Filter Clear | 2        | 35                   |
+--------------+--------------+----------+----------------------+
| Code3        | Filter Clear | 5        | 35                   |
+--------------+--------------+----------+----------------------+
| Code4        | Filter Clear | 10       | 35                   |
+--------------+--------------+----------+----------------------+
| Code5        | Filter Clear | 3        | 35                   |
+--------------+--------------+----------+----------------------+
| Code6        | Filter Clear | 8        | 35                   |
+--------------+--------------+----------+----------------------+
| Code7        | Filter Clear | 2        | 35                   |
+--------------+--------------+----------+----------------------+

Calculating the sum separately

Last but not least, you could aggregate the sum of the counts from a nested select. That way, you'll only get the sum:

SELECT SUM(c)
FROM (
  SELECT COUNT(1) c
  FROM "Table" "R"
  WHERE "STATUS" IN ('LOOKUP CLEAR') 
  AND NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) 
  AND "CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY')  
  AND  TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') 
  AND "STATION_CODE" IN ('Code1','Code2','Code3','Code4','Code5','Code6','Code7') 
  GROUP BY "STATION_CODE", "STATUS"
)

In this case Gordon Linoff's answer is probably better.

Answer 2

You could try this:

with alldata as (
    SELECT "STATION_CODE", "STATUS", COUNT(1) as counts
    FROM "Table" "R"
    WHERE "STATUS" IN ('LOOKUP CLEAR') 
    AND NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) 
    AND "CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY')  
    AND TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') 
    AND "STATION_CODE" IN ('Code1','Code2','Code3','Code4','Code5','Code6','Code7') 
    GROUP BY "STATION_CODE", "STATUS";
)
select station_code, status, counts from alldata
union all
select 'Sum', ' ', sum(counts) from alldata

Answer 3

Remove the group by and just do an overall count:

SELECT COUNT(1) 
FROM "Table" "R"
WHERE "STATUS" IN ('LOOKUP CLEAR') AND
      NOT ("REQUEST_TYPE" IN ('Goodnight', 'Freeform', 'CANCEL')) AND
      "CALL_DATE" BETWEEN TO_DATE('07/12/2015','DD/MM/YYYY') AND 
      TO_DATE ('13/12/2015 23:59:59','DD/MM/YYYY HH24:MI:SS') AND
      "STATION_CODE" IN ('Code1', 'Code2', 'Code3', 'Code4', 'Code5', 'Code6', 'Code7') ;