user2686807 - 1 year ago 56

SQL Question

I have a table like this :

`Server CompliancePercentage`

A 25

B 15

C 45

D 75

E 17

F 82

I want to get from a single query the results in the following way:

`Conformity% 00-20 20-40 40-60 60-80 80-100`

Server Count 2 1 1 1 1

How do I get the above mentioned result from a nested query ?

Any help would be greatful.

Thanks a lot in advance.

Suvi

Answer Source

You can use an aggregate function with a CASE expression to get the result:

```
select
'ServerCount' Conformity,
count(case when CompliancePercentage >= 0 and CompliancePercentage <20 then 1 end) Per00_19,
count(case when CompliancePercentage >= 20 and CompliancePercentage <40 then 1 end) Per20_39,
count(case when CompliancePercentage >= 40 and CompliancePercentage <60 then 1 end) Per40_59,
count(case when CompliancePercentage >= 60 and CompliancePercentage <80 then 1 end) Per60_79,
count(case when CompliancePercentage >= 80 and CompliancePercentage <100 then 1 end) Per80_100
from yourtable;
```