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.