Skip site navigation (1) Skip section navigation (2)

Re: query like this???

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 (view raw or flat)
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/

In response to

Responses

pgsql-novice by date

Next:From: John DeSoiDate: 2005-04-27 17:19:23
Subject: Re: Inserting Using RowType
Previous:From: Greg LindstromDate: 2005-04-27 16:19:04
Subject: Inserting Using RowType

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group