SQL a range in the all dataset

**Steve** · Feb 2 '07, 01:45 AM

Re: SQL a range in the all dataset

On Feb 1, 4:59 pm, Wei ZOU <w...@ucdavis.e duwrote:

-----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?
>
Thanks in advance.
>
Wei
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla -http://enigmail.mozdev .org
>
iD8DBQFFwozpllC A8yArcwwRAlzhAJ 0dXvJeN8r5tCMwb ikokrI9qXok0ACf cWA9
4WJ90KIbVaXu6az nolw8CDE=
=K+6d
-----END PGP SIGNATURE-----

SELECT COUNT(*)
FROM MyTable
WHERE price BETWEEN startPrice AND endPrice;

**AlterEgo** · Feb 2 '07, 02:05 AM

Re: SQL a range in the all dataset

Wei,

This should work for you:

create table #MyTable (Price decimal(9,2), Amount int)

insert #MyTable select 99.5, 10000
insert #MyTable select 99.7, 8000
insert #MyTable select 100, 3000
insert #MyTable select 100.1, 1000
insert #MyTable select 100.5, 500
insert #MyTable select 100.8, 1500
insert #MyTable select 105, 2000
insert #MyTable select 200, 100

select
cast(Price + .5 as int) Price
, sum(Amount) SumOfAmount
from #MyTable
group by
cast(Price + .5 as int)
order by
cast(Price + .5 as int)

-- Bill

"Wei ZOU" <wzou@ucdavis.e duwrote in message
news:epu263$l7n $1@skeeter.ucda vis.edu...

-----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?
>
Thanks in advance.
>
Wei
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
iD8DBQFFwozpllC A8yArcwwRAlzhAJ 0dXvJeN8r5tCMwb ikokrI9qXok0ACf cWA9
4WJ90KIbVaXu6az nolw8CDE=
=K+6d
-----END PGP SIGNATURE-----

**Ed Murphy** · Feb 2 '07, 03:55 AM

Re: SQL a range in the all dataset

AlterEgo wrote:

"Wei ZOU" <wzou@ucdavis.e duwrote in message
news:epu263$l7n $1@skeeter.ucda vis.edu...

>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?

create table #MyTable (Price decimal(9,2), Amount int)
>
insert #MyTable select 99.5, 10000
insert #MyTable select 99.7, 8000
insert #MyTable select 100, 3000
insert #MyTable select 100.1, 1000
insert #MyTable select 100.5, 500
insert #MyTable select 100.8, 1500
insert #MyTable select 105, 2000
insert #MyTable select 200, 100
>
select
cast(Price + .5 as int) Price
, sum(Amount) SumOfAmount
from #MyTable
group by
cast(Price + .5 as int)
order by
cast(Price + .5 as int)

(Please don't top-post. Fixed.)

This is wrong in a couple ways. Steve's attempt is wrong in a couple
other ways. I believe this matches the original specs:

select a.Price, count(b.Amount) TagCount
from #MyTable a
join #MyTable b on abs(a.Price - b.Price) <= 1.0
group by a.Price
order by a.Price

**Wei ZOU** · Feb 3 '07, 12:25 AM

Re: SQL a range in the all dataset

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thank you guys all for the help. Ed's code works. I thought of using a
VB procedure to do this. But now......Very cool!

select a.Price, count(b.Amount) TagCount
from #MyTable a
join #MyTable b on abs(a.Price - b.Price) <= 1.0
group by a.Price
order by a.Price

Ed Murphy wrote:

AlterEgo wrote:
>

>"Wei ZOU" <wzou@ucdavis.e duwrote in message
>news:epu263$l7 n$1@skeeter.ucd avis.edu...

>

>>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?

>

>create table #MyTable (Price decimal(9,2), Amount int)
>>
>insert #MyTable select 99.5, 10000
>insert #MyTable select 99.7, 8000
>insert #MyTable select 100, 3000
>insert #MyTable select 100.1, 1000
>insert #MyTable select 100.5, 500
>insert #MyTable select 100.8, 1500
>insert #MyTable select 105, 2000
>insert #MyTable select 200, 100
>>
>select
> cast(Price + .5 as int) Price
> , sum(Amount) SumOfAmount
>from #MyTable
>group by
> cast(Price + .5 as int)
>order by
> cast(Price + .5 as int)

>
(Please don't top-post. Fixed.)
>
This is wrong in a couple ways. Steve's attempt is wrong in a couple
other ways. I believe this matches the original specs:
>
select a.Price, count(b.Amount) TagCount
from #MyTable a
join #MyTable b on abs(a.Price - b.Price) <= 1.0
group by a.Price
order by a.Price

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFw9ZcllC A8yArcwwRAmrEAJ 9uz9pLcuqPXyjjT +5PcsNliPd6yQCf Qfr3
ttkljmItZFg4q4q SGRB1G6o=
=qTpu
-----END PGP SIGNATURE-----