PostgreSQL equivalent for SQL Server GROUP BY WITH ROLLUP

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 detailunionselect * from accountunionselect * from totalorder by account, event

This is a generic answer to the equivalent of roll up in Postgresql.

Given a 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) totalfrom tgroup by rollup(l1, l2)

This is how to do it in Postgresql: SQL Fiddle

Create aggregating CTEs starting from the detail 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)

Notice that for performance the next up level aggregates from the previous down level.

With that done just union the CTEs creating the appropriate labels:

select l1, l2, totalfrom detailunionselect l1, 'Total', totalfrom l2unionselect 'Total', '', totalfrom l1order by l1, l2