user2686807 user2686807 - 7 months ago 14
SQL Question

Nested SQL Queries: How to get multiple counts on same elements based on different criteria

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

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;

See SQL Fiddle with Demo

Comments