How to categorize records in groups in PostgreSQL? Here is a same schema with data,  

CREATE TABLE groups (
  id       int            PRIMARY KEY,
  name     varchar(30)
);  
CREATE TABLE emp (
  id       int            PRIMARY KEY,
  name     varchar(50),
  group_id int            REFERENCES groups
);  
INSERT INTO groups(id,name) VALUES
  (1, 'Managers'),
  (2, 'Teachers'),
  (3, 'Cleaners'),
  (4, 'Drivers');
INSERT INTO emp(id,name,group_id) VALUES
  (1,'john',1),
  (2,'Smith',1),
  (3,'Evank',2),
  (4,'Doe',3),
  (5,'Jane',4);  


Now I need to categorize groups by the title of Site and have the following result:
I want to count all managers and teachers as site1, cleansers as site2, Drivers as site3 and so on.

Site      Employees  
Site1         3  
Site2         1  
Site3         1
There is no "sites" field in your SQL statements.

I did the following to solve your problem:

(You'll notice that I've taken many more fields than are necessary to do the operation - it's so that you understand where the data is coming from. I often find it very helpful to include far more fields than required in my initial attempts at formulating SQL and then I pare them down as required by the problem!).

WITH emp_group_join AS
(
  SELECT e.id AS eid, e.name AS ename, e.group_id AS egid, 
         g.id AS gid, g.name AS gname
  FROM emp e
  JOIN groups g ON 
     e.group_id = g.id
),
sites AS
(
  SELECT egj.*,
    CASE 
      -- This is necessary to distinguish between Managers + Teachers
      -- and other categories of worker - of course, if you add a site
      -- field, you may not have to do this!

      WHEN gname = 'Managers' THEN 'White Collar'
      WHEN gname = 'Teachers' THEN 'White Collar'
      ELSE gname
    END AS work_type
  FROM emp_group_join egj
)
SELECT work_type, COUNT(work_type) FROM sites
GROUP BY work_type 
ORDER BY work_type DESC;


and the result is:

  work_type   | count 
--------------+-------
 White Collar |     3
 Drivers      |     1
 Cleaners     |     1
(3 rows)


This is how to do it using a subSELECT (same result):

SELECT work_type, COUNT(work_type) AS wt_count
FROM
(
  SELECT e.group_id, 
         g.id, 
         g.name,
         CASE
           WHEN g.name = 'Managers' THEN 'White Collar'
           WHEN g.name = 'Teachers' THEN 'White Collar'
           ELSE g.name
         END AS work_type
  FROM emp e
    JOIN groups g ON 
    e.group_id = g.id
) AS join_tab
GROUP BY work_type
ORDER BY wt_count DESC;


You could try running the WITH clause SELECT and/or the subSELECT statements on their own to see in detail what's happening! p.s. +1 for providing DDL and DML!