How to group the results of a GROUP BY Query in SQL?

Question

My SQL Query is:

SELECT Comments, COUNT(Comments) [Total] 
FROM Completed_Scrubs 
GROUP BY Comments

The result is:

Comments    Total
------------------
Cus           202
WEA             1
Process        13
Rework         30
Non           893
Prob            1
App            10

I want to add the different rows as:

(Cus + WEA) = Uncontrolled
(Process + Rework) = Controlled
(Non+Prob+App) = Business

So the result should be:

Comments        Total
----------------------
Uncontrolled     203
Controlled        43
Business         904

Any help will be highly appreciated.

Answer 1

Very simple solution, count each type and union the results together:

select 'Uncontrolled', count(*)
from Completed_Scrubs where comments in ('Cus', 'WEA')
union all
select 'Controlled', count(*)
from Completed_Scrubs where comments in ('Process', 'Rework')
union all
select 'Business', count(*)
from Completed_Scrubs where comments in ('Non', 'Prob', 'App')

Or a little bit more advanced:

select status, count(*) from
(select case when comments in ('Cus', 'WEA') then 'Uncontrolled' 
             when comments in ('Process', 'Rework') then 'Controlled' 
             when comments in ('Non', 'Prob', 'App') then 'Business' 
             else 'Invalid' end as status
 from Completed_Scrubs)
group by status

Answer 2

You can make use of a CASE statement here to define your output and in the GROUP BY

SELECT 
(CASE WHEN Comments in ('Cus','WEA') THEN 'Uncontrolled'
      WHEN Comments in ('Process','Rework') THEN 'Controlled'
      WHEN Comments in ('Non','Prob','App') THEN 'Business'
 END) as Comments,
COUNT(Comments) [Total] 
FROM Completed_Scrubs 
GROUP BY (CASE WHEN Comments in ('Cus','WEA') THEN 'Uncontrolled'
              WHEN Comments in ('Process','Rework') THEN 'Controlled'
              WHEN Comments in ('Non','Prob','App') THEN 'Business'
          END)