Re: crosstab function

That did it.It's the smallest things that always kill you.

Thanks

Sim

On 12/14/2010 05:00 PM, Filip Rembiałkowski wrote:

>
> 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 value columns, 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
> <mailto: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 <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
>>
>>
>
>