Date: Tue, 6 Feb 2007 07:58:25 +0100
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Search a range of cases/records
Message-ID: <20070206065825.GB6761@a-kretschmer.de>
References: <45C253FC.2040002@ucdavis.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45C253FC.2040002@ucdavis.edu>
User-Agent: Mutt/1.5.9i

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