I have a column with Different BMI Ranging different numbers . I want to the count BMI that falls under the following ranges
0-18.5
18.5- 24.5
24.5-29.9
>30 given these ranges and not mentioned in the table
And I want to display them display in the table as such like if less than 18.5 count is 30
range BMI
0 - 18.5 30
18.5- 24.5
24.5-29.9
30
And I have one more column called disease whose values are yes and no. I want to canulate only for those instances which are yes for the above ranges.
SELECT
SUM(CASE WHEN BMI < 18.5 THEN 1 ELSE 0 END) AS [0-18.5],
SUM(CASE WHEN BMI < 24.5 AND BMI >=18.5 THEN 1 ELSE 0 END) AS [18.5-24.5],
SUM(CASE WHEN BMI < 29.9 AND BMI >=24.5 THEN 1 ELSE 0 END) AS [24.5-29.9],
SUM(CASE WHEN BMI > 29.9 THEN 1 ELSE 0 END) AS [>29.9]
FROM
YourTable;
To add the disease condition, add another caluse to each WHEN clause like "AND disease = 'YES'"
drop table if exists #BodyMass
create table #BodyMass ( Value Float )
insert into #BodyMass select 10.5
insert into #BodyMass select 11.6
insert into #BodyMass select 20.4
insert into #BodyMass select 28.9
insert into #BodyMass select 27.3
insert into #BodyMass select 35
insert into #BodyMass select 38
insert into #BodyMass select 39
drop table if exists #LookUp
create table #Lookup ( id int ,descr varchar(10),minrange float , maxrange float , value int )
insert into #Lookup select 1,'0-18.5' , 0.0 , 18.5 , 1
insert into #Lookup select 2,'18.6-24.5',18.6 , 24.5 , 2
insert into #Lookup select 3,'24.6-29.9',24.6 , 29.9 , 3
insert into #Lookup select 4,'>29.9' ,30.0 , 100 , 4
select * from #Lookup
SELECT
b.descr
, count(b.value)
FROM
#BodyMass a
join
#Lookup b
on a.Value between b.minrange and b.maxrange
GROUP BY
b.id,b.descr
ORDER BY
b.id
