Thread: Problem with SELECT

I have a following table
-----+--------
 day | quantity
-----+--------

where day is a date and quantity is an integer value. Now I need to
make a SELECT statement on this table which returns me a full
information on a whole month, it means day>='2007-10-01' and day
<'2007-11-01' but if there is no any quantity for some days a need to
have 0 as a result. Assume that I have following records:

INSERT INTO tmp_table(day, quantity) VALUES('2007-10-01', 23);
INSERT INTO tmp_table(day, quantity) VALUES('2007-10-04', 23);
INSERT INTO tmp_table(day, quantity) VALUES('2007-10-09', 23);

How can I make a SELECT statement to receive:
-----------------+--------
 day            | quantity
-----------------+--------
2007-10-01 | 23
2007-10-02 | 0
2007-10-03 | 0
2007-10-04 | 23

and so on...?

am  Mon, dem 08.10.2007, um  3:28:04 -0700 mailte marwis1978 folgendes:
> I have a following table
> -----+--------
>  day | quantity
> -----+--------
>
> where day is a date and quantity is an integer value. Now I need to
> make a SELECT statement on this table which returns me a full
> information on a whole month, it means day>='2007-10-01' and day
> <'2007-11-01' but if there is no any quantity for some days a need to
> have 0 as a result. Assume that I have following records:

No problem, generate_series() can do the job. Example:

test=*# select * from t1;
     d      | val
------------+-----
 2007-10-08 |   1
 2007-10-13 |   5
(2 rows)

test=*# select current_date+s, coalesce(val,0) from
generate_series(0,5)s left outer join t1 on(current_date+s = d);
  ?column?  | coalesce
------------+----------
 2007-10-08 |        1
 2007-10-09 |        0
 2007-10-10 |        0
 2007-10-11 |        0
 2007-10-12 |        0
 2007-10-13 |        5
(6 rows)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net