Thread: query like this???
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
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/
On Wed, 2005-04-27 at 10:43 -0600, Michael Fuhr wrote:
> 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:
>
First thing first, this is the table:
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 );
INSERT INTO stupid VALUES( '2004-12-9', 5 );
INSERT INTO stupid VALUES( '2004-10-9', 7 );
Thank you. I use PostgreSQL 8.0.2. That help me much... but I m stuck
with the special case now. Consider this query:
SELECT extract( year FROM datestock::timestamp ) AS year,
extract( month FROM datestock::timestamp ) AS month, sum(dummy) AS total
FROM stupid
WHERE datestock BETWEEN '2004-11-01' AND '2005-08-15'
GROUP BY year, month
ORDER BY year, month
will give this output:
year month total
2004 12 5
2005 4 9
2005 5 6
2005 7 6
What query will give this output:
year month total
2004 11 0
2004 12 5
2005 1 0
2005 2 0
2005 3 0
2005 4 9
2005 5 6
2005 6 0
2005 7 6
2005 8 0
but with this condition
WHERE datestock BETWEEN '2004-11-01' AND '2005-08-15'
I'll try this. Not work.
SELECT j.year, g.month, coalesce(sum(dummy), 0) FROM
generate_series( 1, 12 ) AS g(month)
LEFT OUTER JOIN stupid AS s ON extract(month FROM datestock::timestamp)
= g.month
RIGHT OUTER JOIN generate_series( 2004, 2005 ) AS j(year) ON
extract(year FROM datestock::timestamp) = j.year
WHERE datestock BETWEEN '2004-11-01' AND '2005-08-15'
GROUP BY j.year, g.month
ORDER BY j.year, g.month
Thank you.
Regards,
Akbar