How to group by a param but also include the total aggregated (ungrouped by)?

Question

I have a table in Postgres 9.3.6 that looks like this:

utm_source    value
fb            12
fb            8
google        3
google        4
null          5

I want to run a SQL query to sum value by utm_source, but also include the total value as well. So the final table should look like:

utm_source    sum
fb            20
google        7
null          5
all           32

This is what I'd otherwise use:

SELECT SUM(value) as sum FROM my_table GROUP BY utm_source;

But I want the all in there as well!

ROLLUP or GROUPING SETS are some options, and has several options covered in this question: http://stackoverflow.com/q/14478361/84206 — AaronLS, Apr 03 '15 at 21:58
Please tag your DBMS as there may be solutions that are platform specific. — crthompson, Apr 03 '15 at 22:16

score 1 · Answer 1 · edited Apr 04 '15 at 04:50

1

SELECT isnull(utm_source,'')
       ,SUM(value) as sum 
FROM my_table 
GROUP BY isnull(utm_source,'') with rollup

you can update the NULL to 'all' later.

edited Apr 04 '15 at 04:50

Vivek S.

19,945
7
68
85

answered Apr 03 '15 at 22:26

Zee

830
1
9
22

The OP did not clarify the RDBMS at first. It's Postgres, `rollup` is a non-standard feature of SQL Server and does not work in Postgres. Details: http://dba.stackexchange.com/questions/94817/grouping-equivalent-in-postgresql/94822#94822 – Erwin Brandstetter Apr 03 '15 at 23:51

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Using a CTE so the base table is only scanned once, which is typically faster. There is some overhead cost for the CTE, so it may not pay for trivially small tables.

WITH cte AS (
   SELECT utm_source, sum(value) AS sum_value
   FROM   tbl
   GROUP  BY 1
   )
SELECT * FROM cte
UNION ALL  -- not just UNION !
SELECT 'all', sum(sum_value) FROM cte;

SQL Server has a non-standard extension for the task using the grouping() and rollup() keywords. Here is how to implement it in Postgres:

Grouping() equivalent in PostgreSQL?

How to group by a param but also include the total aggregated (ungrouped by)?

2 Answers2