|
I rechecked and with products as columns it has duplicate customers.
My goal is one row per customer with the sum of quantity filled in
for each product they purchased. create table customers(customerid serial primary key, customername text); create table products(productid serial primary key, productname text); create table quotations(quotationid serial primary key, customerid int, orderdate timestamp); create table quotationitems(quotationitemid serial primary key, quotationid int, productid int, quantity int, unitprice numeric(9,2)); select * from crosstab(' select customername,productname as bucket,sum(quantity) as bucketvalue from quotationitems a join quotations b using(quotationid) join customers c using(customerid) join sales.products d using (productid) where orderdate between ''1/1/2009'' and ''1/1/2010'' and producttypeid=1 group by customername,productname order by productname', 'select productname from sales.products where producttypeid=1 order by productname') as rpt(customername text,"ATX" int, "CM-A510" int, "CM-F82" int, "CM-i586" int, "CM-i686B" int, "CM-i686M" int, "CM-iAM" int, "CM-iGLX" int, "CM-iPM" int, "CM-iTC" int, "CM-T3530" int, "CM-X255" int, "CM-X270" int, "CM-X300" int, "CM-XAM" int ) order by customername On 12/14/2010 10:27 AM, Filip RembiaÅkowski wrote: please show |
