Check for individual row in a group query in PostgreSQL

Question

WITH 
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'), 
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'), 
            ('D2','E12', 'Role1')
)

For each department, fetch the number of individual employees for each role.
The output should be:

#_of_Role1_Employees; #_of_Role2_Employees
2; 1
1; 0

+1 for providing ready-to-use testdata – Feb 14 '13 at 10:47 — , Feb 14 '13 at 10:47
Can there be duplicate entries per `(DName, EName, Role)`? – Erwin Brandstetter Feb 14 '13 at 10:51 — Erwin Brandstetter, Feb 14 '13 at 10:51
Yes Erwin, there can be duplicate entries. – Gopal Feb 14 '13 at 11:16 — Gopal, Feb 14 '13 at 11:16

Clodoaldo Neto · Accepted Answer · 2013-02-14T11:29:57.680

WITH 
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'), 
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'), 
            ('D2','E12', 'Role1')
)
select
    dname "Dname",
    count(role = 'Role1' or null) "Role1",
    count(role = 'Role2' or null) "Role2"
from emp
group by dname
;
 Dname | Role1 | Role2 
-------+-------+-------
 D1    |     2 |     1
 D2    |     1 |     0

Update

This should match the distinct requirement:

WITH
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'),
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'),
            ('D2','E12', 'Role1')
)
select
    dname "Dname",
    count(role = 'Role1' or null) "Role1",
    count(role = 'Role2' or null) "Role2"
from (
    select distinct *
    from emp
) s
group by dname
;
 Dname | Role1 | Role2 
-------+-------+-------
 D1    |     2 |     1
 D2    |     1 |     0

Its very elegant Clodoaldo, however, I missed to mention that duplicates are allowed in the test data. Is there a way to maintain that uniqueness, as a extension of this query? — Gopal, Feb 14 '13 at 11:20

score 0 · Answer 2 · answered Feb 14 '13 at 09:46

Within the department Group, each employee row is to be checked for the role, and the distinct employee names are counted.

Select COUNT(Distinct(CASE WHEN Role = 'Role1' THEN EName END)) As "#_of_Role1_Employees",
       COUNT(Distinct(CASE WHEN Role = 'Role2' THEN EName END)) As "#_of_Role2_Employees"
from Emp
Group by DName

score 0 · Answer 3 · edited May 23 '17 at 10:32

The fact that there can be duplicate entries per (dname, ename, role) is indication for an over-simplified test case. I suspect the test case should really be:

CREATE TEMP TABLE emp(dname text, ename text, role text, col1 text);
INSERT INTO emp VALUES
    ('D1','E11', 'Role1', 'foo1')
   ,('D1','E11', 'Role2', 'foo2')
   ,('D1','E12', 'Role1', 'foo3')
   ,('D1','E12', 'Role1', 'foo4')
   ,('D2','E12', 'Role2', 'foo5');

Then @Clodoaldo's (otherwise fine) query would have to use:

SELECT DISTINCT dname, ename, role

instead of:

SELECT DISTINCT *

An alternative would be a crosstab query:

SELECT * FROM crosstab(
      $x$
      SELECT dname, role, count(DISTINCT ename)::int
      FROM   emp
      GROUP  BY dname, role
      $x$

      ,$y$SELECT DISTINCT role FROM emp ORDER BY 1$y$)
AS ct (dname text, "Role1" text, "Role2" int);

You need the tablefunc installed for this. More details for this query in this related answer:
PostgreSQL Crosstab Query

You have to list all possible roles in the column definition list.

Check for individual row in a group query in PostgreSQL

3 Answers3

Update