Need help to filter data

[AK.007]

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.

[JamesK]

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'"

[AK.007]

@JamesK

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;

Thank you so much JamesK. You made my work easy . I was using count function instead of sum and was unable to so so.