Re: crosstab function

[Sim Zacks <sim@xxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx>

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