October 17, 2007 at 9:36 am
so I have a table that has a field that is numeric
the numbers are all over the map and we have a client that needs these 3 aggregates from it.
the number of values in a range of values and the percentage of the whole that is in that range.
I.E. count from 0 to field value X, count from field value X to Value Y ...etc
he also needs (in the same query) the range that the percentage includes
I.E. the output should look like
count | field value range | percent of total count in this range
and example output would be
58 | 0 to 75 | 12%
does this make sense?
October 17, 2007 at 9:44 am
If you can live with showing the low end and high end without putting the word "to" between them try something like this
declare @lowend int
declare @highend int
select @lowend=0, @highend=75
select @lowend as LowerBound, @highend as UpperBound,
sum(case when fieldname between @lowend and @highend then 1 else 0 end) as RangeCount,
sum(case when fieldname between @lowend and @highend then 1 else 0 end)/count(*) as RangePerc
from tablename
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 9:47 am
this is perfect,
thanks so much
October 17, 2007 at 9:52 am
oops, I lied, this isn't perfect. but it's close, the problem is, we don't know our low end and high end before hand. so we need to calculate that on the fly. I.E.
find out which number is the highest of the bottom 25%
which number is the highest of the 26 to 50 percentile...etc
but all of it needs to be in the same query, or I guess we could do it in
a temp table and then aggregate it. But It's been a while since I did
something like this so I don't know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply