Posted by AlterEgo on 02/02/07 01:30

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.edu> wrote in message
news:epu263$l7n$1@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
>
> iD8DBQFFwozpllCA8yArcwwRAlzhAJ0dXvJeN8r5tCMwbikokrI9qXok0ACfcWA9
> 4WJ90KIbVaXu6aznolw8CDE=
> =K+6d
> -----END PGP SIGNATURE-----

[Back to original message]