|
|
Posted by Ed Murphy on 02/02/07 03:46
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
Navigation:
[Reply to this message]
|