PostgreSQL equivalent for SQL Server GROUP BY WITH ROLLUP

Question

I have a Sql server request that uses the ROLLUP clause during grouping. I want to get an equivalent request in Postgres. Request on SQl server:

 SELECT (CASE WHEN acnt_dba_name Is Null THEN 'Total' ELSE acnt_dba_name END) as account, (CASE WHEN evt_name Is Null THEN '' ELSE evt_name END) as event, COUNT(CASE reg_is_complete WHEN true THEN 1 ELSE Null END) as regsComplete, COUNT(CASE WHEN reg_frn_pro_id > 0 AND reg_is_complete = false THEN 1 ELSE Null END) as regsInComplete, COUNT(CASE WHEN reg_frn_pro_id > 0 THEN Null ELSE 1 END) as regsClicks FROM registrations_view LEFT JOIN events ON (evt_id = reg_frn_evt_id) LEFT JOIN accounts ON (acnt_id = evt_frn_acnt_id) WHERE reg_date_created < #CreateODBCDate(url.endDate)# AND reg_date_created > #CreateODBCDate(url.startDate)# AND reg_is_active = true -- only active regs AND reg_is_test = false -- only live registrations -- AND reg_is_denied = false -- exclude denied reg statuses (include these for now RWB 8/7/2) GROUP BY rollup(acnt_dba_name, evt_name) -- Sort with Nulls at the bottom ORDER BY acnt_dba_name, evt_name 

Answer 1

 with detail as ( select acnt_dba_name as account, evt_name as event, count(case reg_is_complete when true then 1 else null end) as regscomplete, count(case when reg_frn_pro_id > 0 and reg_is_complete = false then 1 else null end) as regsincomplete, count(case when reg_frn_pro_id > 0 then null else 1 end) as regsclicks from registrations_view left join events on evt_id = reg_frn_evt_id left join accounts on acnt_id = evt_frn_acnt_id where reg_date_created < #CreateODBCDate(url.endDate)# AND reg_date_created > #CreateODBCDate(url.startDate)# and reg_is_active = true -- only active regs and reg_is_test = false -- only live registrations group by acnt_dba_name, evt_name ), account as ( select account, '' as event, sum(regscomplete) as regscomplete, sum(regsimcomplete) as regsincomplete, sum(regsclicks) as regsclicks from detail group by account ), total as ( select 'Total' as account, '' as event, sum(regsComplete) as regsComplete, sum(regsImComplete) as regsInComplete, sum(regsClicks) as regsClicks from account ) select * from detail union select * from account union select * from total order by account, event 

Answer 2

This is a general response to the collapse equivalent in Postgresql.

Given table t:

 create table t (l1 char(1), l2 char(1), i integer); insert into t (l1, l2, i) values ('A', 'X', 1), ('A', 'Y', 2), ('B', 'X', 3), ('B', 'Y', 4); 

And this SQL Server query: SQL Fiddle

 select l1, l2, sum(i) total from t group by rollup(l1, l2) 

Here's how to do it in Postgresql: SQL Fiddle

Create aggregate CTEs, starting with drillthroughs going to the top level:

 with detail as ( select l1, l2, sum(i) total from t group by l1, l2 ), l2 as ( select l1, sum(total) total from detail group by l1 ), l1 as ( select sum(total) total from l2 ) 

Note that for performance, the next level up is aggregated from the previous level down.

With this, you can simply combine the CTE by creating the appropriate labels:

 select l1, l2, total from detail union select l1, 'Total', total from l2 union select 'Total', '', total from l1 order by l1, l2 

Answer 3

In Postgres 9.5, GROUPING SETS, CUBE and ROLLUP appeared: http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS