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
Nice clipart =)
ReplyDeleteToo bad I haven't found cube in Postgres either.