query like this???

From: Akbar <tuxer(at)myrealbox(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: query like this???
Date: 2005-04-27 16:12:48
Message-ID: 1114618368.6831.7.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

CREATE TEMP table stupid (
datestock date,
dummy int
);

INSERT INTO stupid VALUES( '2005-4-1', 3 );
INSERT INTO stupid VALUES( '2005-5-1', 3 );
INSERT INTO stupid VALUES( '2005-5-4', 3 );
INSERT INTO stupid VALUES( '2005-7-5', 3 );
INSERT INTO stupid VALUES( '2005-7-1', 3 );
INSERT INTO stupid VALUES( '2005-4-7', 3 );
INSERT INTO stupid VALUES( '2005-4-9', 3 );

query like this:
SELECT extract (month FROM datestock::timestamp) AS month, sum(dummy)
FROM stupid
WHERE extract (month FROM datestock::timestamp) BETWEEN 2 AND 8
GROUP BY extract (month FROM datestock::timestamp)
ORDER BY extract (month FROM datestock::timestamp)

give this:
month sum
----- ---
4 9
5 6
7 6

I want to ask what query will give me this output:
month sum
----- ---
2 0
3 0
4 9
5 6
6 0
7 6
8 0

but with this condition:
WHERE extract (month FROM datestock::timestamp) BETWEEN 2 AND 8

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Greg Lindstrom 2005-04-27 16:19:04 Inserting Using RowType
Previous Message Bruce Momjian 2005-04-27 15:59:12 Re: Poor-man's enumeration type