SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 12:13 PM
| ||||
| ||||
 SQL a range in the all dataset -----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-----      |
|
03-01-2008, 12:13 PM
| |||
| |||
| Re: SQL a range in the all dataset On Feb 1, 4:59 pm, Wei ZOU <w...@ucdavis.edu> wrote: > -----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; |
|
03-01-2008, 12:13 PM
| |||
| |||
| 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.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 > > iD8DBQFFwozpllCA8yArcwwRAlzhAJ0dXvJeN8r5tCMwbikokr I9qXok0ACfcWA9 > 4WJ90KIbVaXu6aznolw8CDE= > =K+6d > -----END PGP SIGNATURE----- |
|
03-01-2008, 12:13 PM
| |||
| |||
| Re: SQL a range in the all dataset 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 |
|
03-01-2008, 12:13 PM
| ||||
| ||||
| 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.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+5Pcs NliPd6yQCfQfr3 ttkljmItZFg4q4qSGRB1G6o= =qTpu -----END PGP SIGNATURE----- |
Linear Mode
