Checking a single row in a group query in PostgreSQL
WITH
Emp(DName, EName, Role) AS (
VALUES ('D1','E11', 'Role1'),
('D1','E11', 'Role2'),
('D1','E12', 'Role1'),
('D2','E12', 'Role1')
)
For each department, select the number of individual employees for each role.
The output should be:
#_of_Role1_Employees; #_of_Role2_Employees
2; 1
1; 0
source to share
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 must meet a separate 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
source to share
In a department group, each employee row must be checked for that role and the individual 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
source to share
The fact that there may be duplicate entries behind (dname, ename, role)
is an indication for an oversimplified 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:
SELECT DISTINCT *
An alternative might be to query a crosstab:
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);
For this you need tablefunc
. More details on this request in this response answer:
PostgreSQL cross-forwarding request
You must specify all possible roles in the column definition list.
source to share