Here is a same schema with data,

  id       int            PRIMARY KEY,
  name     varchar(30)
  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

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  
  • as a side note, in PostgreSQL we don't use varchar() like that, text is also stored on the table, except it doesn't have a length constraint. They're both stored internally in the same way. – Evan Carroll Sep 26 '17 at 22:51

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.*,
      -- 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
  SELECT e.group_id, 
           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!

| improve this answer | |

You can use union operator to achieve this like below:

SELECT 'Site1' Site, COUNT(id) Employees FROM 
  SELECT * FROM emp 
  WHERE group_id IN (1,2) 
  GROUP By group_id,id
) AS q1 
SELECT 'Site' || group_id - 1 Site, COUNT(id) 
FROM emp 
WHERE group_id NOT IN (1,2) 
GROUP BY group_id,id 
| improve this answer | |
  • It works but it's ugly! :-) Concatenating an INTEGER to a string? Oooh, wouldn't be allowed in my shop! p.s. in future could you please format your answers something like I have done - makes them far easier to read, and you'll likely get more upvotes that way! – Vérace Sep 26 '17 at 9:21
  • I know its ugly, but that's how the solution is written in question. Thanks for the suggestion though. – Lohit Gupta Sep 26 '17 at 10:30
  • Yes, it "works"... just like I could get from New York to LA on the back of a donkey or in a Ferrari - which would you prefer? Check out my subSELECT solution - also works without such chicanery. – Vérace Sep 26 '17 at 11:20

The normal way to do this in your case is to alter the group table and put the data on that. That all said, there is nothing particularly wrong with @Vérace's idea if this is a one-off query. But normally the mapping itself is data and not something you want to maintain in a query.

  ADD COLUMN site_name text;

UPDATE groups
  SET site_name = 'site1'
  WHERE name IN ('Managers', 'Teachers');
UPDATE groups
  SET site_name = 'site2'
  WHERE name IN ('Cleaners');
UPDATE groups
  SET site_name = 'site3'
  WHERE name IN ('Drivers');

Also makes your query simplier.

SELECT site_name,count(*)
FROM groups AS g
JOIN emp
  AS e ON e.group_id = g.id
GROUP BY site_name
ORDER BY site_name;

 site_name | count 
 site1     |     3
 site2     |     1
 site3     |     1
(3 rows)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.