SQL a range in the all dataset

[Wei ZOU]

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

iD8DBQFFwozpllCA8yArcwwRAlzhAJ0dXvJeN8r5tCMwbikokr I9qXok0ACfcWA9
4WJ90KIbVaXu6aznolw8CDE=
=K+6d
-----END PGP SIGNATURE-----

[Steve]

On Feb 1, 4:59 pm, Wei ZOU <[e-mail]:

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

iD8DBQFFwozpllCA8yArcwwRAlzhAJ0dXvJeN8r5tCMwbikokr I9qXok0ACfcWA9
4WJ90KIbVaXu6aznolw8CDE=
=K+6d
-----END PGP SIGNATURE-----

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

[AlterEgo]

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" <wz**@ucdavis.eduwrote in message
news:ep**********@skeeter.ucdavis.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

iD8DBQFFwozpllCA8yArcwwRAlzhAJ0dXvJeN8r5tCMwbikokr I9qXok0ACfcWA9
4WJ90KIbVaXu6aznolw8CDE=
=K+6d
-----END PGP SIGNATURE-----

[Ed Murphy]

AlterEgo wrote:

"Wei ZOU" <wz**@ucdavis.eduwrote in message
news:ep**********@skeeter.ucdavis.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]

-----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" <wz**@ucdavis.eduwrote in message
news:ep**********@skeeter.ucdavis.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

iD8DBQFFw9ZcllCA8yArcwwRAmrEAJ9uz9pLcuqPXyjjT+5Pcs NliPd6yQCfQfr3
ttkljmItZFg4q4qSGRB1G6o=
=qTpu
-----END PGP SIGNATURE-----