Re: crosstab function

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(at)compulab(dot)co(dot)il <mailto:sim(at)compulab(dot)co(dot)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(at)postgresql(dot)org
>> <mailto:pgsql-general(at)postgresql(dot)org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>