|
|
Posted by Wei ZOU on 02/03/07 00:25
-----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.edu> wrote in message
>> news:epu263$l7n$1@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+5PcsNliPd6yQCfQfr3
ttkljmItZFg4q4qSGRB1G6o=
=qTpu
-----END PGP SIGNATURE-----
[Back to original message]
|