INNER JOIN and Count POSTGRESQL

Question

I am learning postgresql and Inner join I have following table. Employee

Id Name    DepartmentId
1  John S.    1
2  Smith P.   1
3  Anil K.    2

Department

Department
Id Name
1  HR
2  Admin

I want to query to return the Department Name and numbers of employee in each department.

SELECT Department.name , COUNT(Employee.id) FROM Department INNER JOIN Employee ON Department.Id = Employee.DepartmentId Group BY Employee.department_id;

I dont know what I did wrong as I am new to database Query.

Answer 1

When involving all rows or major parts of the "many" table, it's typically faster to aggregate first and join later. Certainly the case here, since we are after counts for "each department", and there is no WHERE clause at all.

SELECT d.name, COALESCE(e.ct, 0) AS nr_employees
FROM   department d
LEFT   JOIN (
   SELECT department_id AS id, count(*) AS ct
   FROM   employee
   GROUP  BY department_id
   ) e USING (id);

Also made it a LEFT [OUTER] JOIN, to keep departments without any employees in the result. And COALESCE to report 0 employees instead of NULL in that case.

Related, with more explanation:

• Query with LEFT JOIN not returning rows for count of 0

Your original query would work too, after fixing the GROUP BY clause:

SELECT department.name, COUNT(employee.id)
FROM   department
INNER  JOIN employee ON department.id = employee.department_id
Group  BY department.id;  --!

That's assuming department.id is the PRIMARY KEY of the table, in which case it covers all columns of that table, including department.name. And you may want LEFT JOIN like above.

Aside: Consider legal, lower-case names exclusively in Postgres. See:

• Are PostgreSQL column names case-sensitive?