I finally understand why the query looks like it does, even though it is not what I wanted. Here is the setup:
Version
------------------------------------
"PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit"
Table Structure
------------------------------------
-- Table: modvalues
-- DROP TABLE modvalues;
CREATE TABLE modvalues
(
parties character varying,
baloons character varying,
color character varying,
dayofpurchase date,
someint serial NOT NULL,
amountpur integer,
CONSTRAINT wfe PRIMARY KEY (someint)
)
WITH (
OIDS=FALSE
);
ALTER TABLE modvalues OWNER TO postgres;
Test Data
------------------------------------
insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','red','1/1/2011',7);
insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','green','1/2/2011',14);
insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','blue','1/2/2011',3)
Query
------------------------------------
with a as (select distinct baloons, color,amountpur from modvalues),
b as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-01'),
c as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-02'),
d as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-03')
select
a.baloons,
a.color,
b.amountpur as "Jan First",
c.amountpur as "Jan Second",
d.amountpur as "Jan Third"
from
a left join b on a.baloons=b.baloons
left join c on a.baloons=c.baloons
left join d on a.baloons=d.baloons
Output
------------------------------------
"baloons";"color";"Jan First";"Jan Second";"Jan Third"
"big";"red";7;3;
"big";"red";7;14;
"big";"blue";7;3;
"big";"blue";7;14;
"big";"green";7;3;
"big";"green";7;14;
Issue
------------------------------------
I now see that it is putting 7's in for "Jan First" because I told it to put b.amountpur in there - but why are not rows 3-6 of the output blank for "Jan First" since there were not purchases made on that date for blue and green "color"'s? Is there a way to pivot the data so that it can lay out the data like this:
baloons color jan first jan second jan third
big red 7 null null
big green null 14 null
big blue null null 3
?
