Re: query like this???

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