From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Akbar <tuxer(at)myrealbox(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: query like this??? |
Date: | 2005-04-27 16:43:40 |
Message-ID: | 20050427164340.GA88491@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Apr 27, 2005 at 11:12:48PM +0700, Akbar wrote:
>
> 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
Here's one way:
SELECT g.month, coalesce(sum(s.dummy), 0) AS sum
FROM generate_series(2, 8) AS g(month)
LEFT OUTER JOIN stupid AS s ON extract(month FROM s.datestock) = g.month
GROUP BY g.month
ORDER by g.month;
The generate_series() function comes with PostgreSQL 8.0 and later,
but it's easily written in earlier versions. Here's a simple
example:
CREATE FUNCTION generate_series(integer, integer)
RETURNS SETOF integer AS '
DECLARE
i integer;
BEGIN
FOR i IN $1 .. $2 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql VOLATILE STRICT;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From | Date | Subject | |
---|---|---|---|
Next Message | John DeSoi | 2005-04-27 17:19:23 | Re: Inserting Using RowType |
Previous Message | Greg Lindstrom | 2005-04-27 16:19:04 | Inserting Using RowType |