Friday, April 27, 2012

MySQL has WITH ROLLUP, PostgreSQL users must be fuming with envy




Data sample:

create table ProductInventory(
  ProductCode varchar(10) not null,
  Location varchar(50) not null
);


insert into ProductInventory(ProductCode,Location) values
('CPU','US'),
('CPU','PH'),
('CPU','PH'),
('KB','PH'),
('KB','US'),
('KB','US'),
('MSE','US'),
('MSE','JP');


MySQL query works :

select ProductCode, 
    SUM(Location = 'US') as UsQty,
    SUM(Location = 'PH') as PhilippinesQty
from ProductInventory
group by ProductCode with rollup

Postgres query doesn't work:

select ProductCode, 
    SUM((Location = 'US')::int) as UsQty,
    SUM((Location = 'PH')::int) as PhilippinesQty
from ProductInventory
group by ProductCode with rollup




Output:
ProductCode UsQty PhilippinesQty
CPU             1       2
KB              2       1
MSE             1       0
                4       3


Allay our worries Postgres users, ROLLUP for Postgres is in the pipeline too: http://wiki.postgresql.org/wiki/Grouping_Sets

1 comment: