Re: crosstab function

http://www.postgresql.org/docs/8.4/interactive/tablefunc.html says:

> The crosstab function produces one output row for each consecutive group
> of input rows with the same row_name value. It fills the output valuecolumns, left to right, with the
> value fields from these rows. If there are fewer rows in a group than
> there are output value columns, the extra output columns are filled with
> nulls; if there are more rows, the extra input rows are skipped.
>
> In practice the SQL query *should always specify ORDER BY 1,2* to ensure
> that the input rows are properly ordered, that is, values with the same
> row_name are brought together and correctly ordered within the row. Notice
> that crosstab itself does not pay any attention to the second column of
> the query result; it's just there to be ordered by, to control the order in
> which the third-column values appear across the page.
>

I don't have time to verify this but I guess this is your problem, try and
test
"group by customername,productname order by customername, productname"
instead of
"group by customername,productname order by productname"

HTH

Filip

W dniu 14 grudnia 2010 11:45 użytkownik Sim Zacks <sim(at)compulab(dot)co(dot)il>napisał:

> 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>
>
>> 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)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>