Just to clarify, there aren't duplicate rows. But if the customer purchased two products there are 2 rows for that customer, one with the quantity sum filled in for the first product and the second row with the quantity sum filled in for the second product. I want one customer row with all the items purchased filled in.
Sim
On 12/14/2010 12:45 PM, Sim Zacks wrote:
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
- source data structures (in form of CREATE TABLE please)
- actual whole query that creates duplicates
2010/12/14 Sim Zacks
<sim@compulab.co.il>postgres 8.2.17
I am trying out the crosstab function (tablefunc contrib) for reporting needs and I'm having a problem.
I have customers and products and the data is the quantity purchased. I am grouping by customername, productname in the source sql. My category sql depends if I want the products or customers to be the columns.
When I make customers the rows and products the columns, it works fine. But when I make customers the columns and products the rows, there are duplicate product rows.
Is there a way to group the product rows so that the data results come back correct?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
