Problem with SELECT
Problem with SELECT
От:
marwis1978 <marwis1978@gmail.com>
Дата:
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...?
Re: Problem with SELECT
От:
"A. Kretschmer" <andreas.kretschmer@schollglas.com>
Дата:
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
