SQL CASE statement and Aggregation

I find the CASE statement in SQL very good when it comes to classify data within ranges. Suppose you have a table called DEVICE which logs errors from some kind of machines and you wanted to classify the errors into categories according to the frequency of their occurance. Here is how you could use the CASE statement



Create the test table


DROP TABLE DEVICE;

CREATE TABLE DEVICE
(
DID NUMBER,
DNAME VARCHAR2(50),
DDATE DATE,
DMESSAGE VARCHAR2(50)
);


insert into device values (1,'Engine 5','09-MAR-08','leak');
insert into device values (1,'Engine 5','10-MAR-08','leak');
insert into device values (3,'Cam Belt','10-MAR-08','broken');
insert into device values (3,'Cam Belt','11-MAR-08','broken');
insert into device values (3,'Cam Belt','12-MAR-08','broken');
insert into device values (3,'Cam Belt','13-MAR-08','broken');
insert into device values (3,'Cam Belt','14-MAR-08','broken');
insert into device values (5,'Cockpit','24-MAR-08','lights out');
insert into device values (5,'Cockpit','25-MAR-08','lights out');
insert into device values (5,'Cockpit','23-MAR-08','lights out');
insert into device values (7,'Deck 34','29-MAR-08','starboard light green');
insert into device values (7,'Deck 34','28-MAR-08','starboard light green');
insert into device values (7,'Deck 34','28-MAR-08','starboard light green');
insert into device values (7,'Deck 34','31-MAR-08','starboard light green');
insert into device values (7,'Deck 34','30-MAR-08','starboard light green');
insert into device values (7,'Deck 34','05-APR-08','starboard light green');
insert into device values (7,'Deck 34','04-APR-08','starboard light green');

COMMIT;


And this is a table which would normally aggregate like this:

DNAME     COUNT(*)                                                                                                                           
--------  --------                                                                                                                              
Engine 5  2                                                                                                                              
Cockpit   3                                                                                                                             
Deck 34   7                                                                                                                             
Cam Belt  5  

Now let's use the CASE statement


select dname,
sum(case when cnt between 1 and 5 then cnt else 0 end) "NORMAL LEVEL",
sum(case when cnt between 6 and 11 then cnt else 0 end) "TOLERABLE LEVEL",
sum(case when cnt between 12 and (select count(*) from device) then cnt else 0 end) "DANGEROUS LEVEL"
from
(
select dname, count(*) cnt from device
group by dname
)
group by dname;


And here is the resultset with the CASE statement categorizing the aggregation by range.

DNAME     NORMAL LEVEL  TOLERABLE LEVEL  DANGEROUS LEVEL                                                                                                                       
--------  ------------  ---------------  ---------------                                                                                                                          
Engine 5  2             0                0                                                                                                 
Cockpit   3             0                0                                                                                                
Deck 34   0             7                0                                                                                                
Cam Belt  5             0                0