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