Report counts on ranges
-
Hi All,
I'm trying to accomplish the following:
Get counts based on TIV ranges, for example:
Range Count
Less than 10 20
10 to 20 10
20 to 100 5
GT 100 2
How can I accomplish this via SQL. In my raw count I would have say
something like this:
10 3
11 2
15 5
20 2
How could I roll up to only the ranges, where in my final result I'd have this
10-20 10
This is what I have so far:
SELECT
COUNT(tiv) AS "TIV COUNT",tiv
FROM
temptable
WHERE
GROUP BY
tiv
ORDER BY
tiv
Thanks everyone
-
You can use a CASE statement to help roll things up. At least horizontally.
select
case when fieldA < 10 then 1 else 0 end 'less than 10'
case when fieldA >= 10 and fieldA < 20 then 1 else 0 end 'first range'
etc.
then you could sum these up and get a list horizontally.
create table mytest
( MyID int
, MyVal int
)
go
insert MyTest select 5, 1
insert MyTest select 10, 3
insert MyTest select 11, 2
insert MyTest select 15, 5
insert MyTest select 20, 5
insert MyTest select 22, 3
go
select
sum( lo) 'Lo (less than 10)'
, sum( mid) 'mid (10-20)'
, sum( hi) 'hi (> 20)'
from (
select
case when MyID < 10 then 1 else 0 end 'lo'
, case when MyID >= 10 and MyID < 20 then 1 else 0 end 'mid'
, case when MyID >= 20 then 1 else 0 end 'hi'
from MyTest
 
ago
drop table MyTest
If you want vertically, you'd change the query to:
select
'less than 10'
, sum( Myval) 'lo'
from MyTest
where MyID < 10
union
select
'10-20'
, sum( MyVal) 'mid'
from MyTest
where MyID >= 10
and MyID < 20
union
select
'> 20'
, sum( MyVal) 'hi'
from MyTest
where MyID >= 20
Not that scalable, but for a static list, it works.
-
This is another way to do it assuming you have a table of ranges and descriptions of ranges.
First example displays the ranges. Second example displays range names.
SELECT dbo.MyRanges.LowValue, dbo.MyRanges.HighValue, COUNT(*) AS Expr1
FROM dbo.MyRanges CROSS JOIN
dbo.MyData
WHERE (dbo.myData.Valuefield BETWEEN dbo.MyRanges.LowValue AND
dbo.MyRanges.HighValue)
GROUP BY dbo.MyRanges.LowValue, dbo.MyRanges.HighValue
SELECT COUNT(*) AS Expr1, dbo.MyRanges.RangeName
FROM dbo.MyRanges CROSS JOIN
dbo.MyData
WHERE AND (dbo.MyData.ValueField BETWEEN dbo.MyRanges.LowValue AND
dbo.MyRanges.HighValue)
GROUP BY dbo.MyRanges.LowValue, dbo.MyRanges.HighValue, dbo.MyRanges.RangeName
-
WOW,
Thanks for the responses. I'll start using the queries and will let you know how I make out.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply