PostgreSQL equivalent for SQL Server GROUP BY WITH ROLLUP
12,442
Solution 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
Solution 2
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) total
from t
group 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, total
from detail
union
select l1, 'Total', total
from l2
union
select 'Total', '', total
from l1
order by l1, l2
Author by
Satish Sharma
I Am a Software Developer having an experience of 1 year in J2EE technologies. Past Work Experience of HCL and Presently working in Info-Objects Software's.
Updated on July 21, 2022Comments
-
Satish Sharma almost 2 years
I have an Sql Server Query that is using the
ROLLUP
clause while grouping. I want an equivalent query in Postgres. Query in SQl Server is: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
-
Karl Bartel over 10 yearsYou should use
UNION ALL
instead ofUNION
, so the DB won't have to try to remove duplicates.