am Thu, dem 01.02.2007, um 12:56:28 -0800 mailte Wei ZOU folgendes: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Greetings: > > I have a dataset of two columns: > price amount > 99.5 10000 > 99.7 8000 > 100 3000 > 100.1 1000 > 100.5 500 > 100.8 1500 > 105 2000 > 200 100 > etc > I have to write a SQL query on how many price tags are within [price+-1] > such as 98.5 to 100.5, 100+-1,etc for each records. > Here I know the price tags counts are 5 for 99.5-100.5, 5 for > 99.7-100.7, 6 for 99-101, etc > How should I do for all of the records?
Perhaps something like below: test=*# select s, sum(case when price.price between s-0.5 and s+0.5 then 1 else 0 end) as "+-1", sum(case when price.price between s-1.5 and s+1.5 then 1 else 0 end) as "+-2" from price, generate_series(95, 105) as s group by s order by s; s | +-1 | +-2 -----+-----+----- 95 | 0 | 0 96 | 0 | 0 97 | 0 | 0 98 | 0 | 1 99 | 1 | 5 100 | 5 | 6 101 | 2 | 6 102 | 0 | 2 103 | 0 | 0 104 | 0 | 1 105 | 1 | 1 (11 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
