Why do I get total count while using a group by clause?-CodePudding

I am trying to count employees (from employees table) per division (departments table) My approach was to

SELECT d.division, COUNT (first_name) 
FROM departments d, employees
GROUP BY d.division

I receive a total count of all (1000) employees multiplied by a number of identical divisions (with different departments), e.g. if 'Hardware' division has 'Automotive' and 'Tools' departments, I get a count of 2000.

Counting it within one table works fine (departments are both inside the 'employees' and 'departments' tables)

SELECT department, COUNT (first_name) 
FROM employees
GROUP BY department

{I have just started and there's not many ways to get feedback while studying by yourself}

CodePudding user response：

Your query specifies

FROM departments, employees

but is missing a WHERE clause, so there’s nothing to restrict only employees in the specific division being counted for each division, instead including every employee in every division (see cross join).

Use modern join syntax specifying how the join should be made via the ON keyword, choosing a LEFT (ie outer) join so divisions without any employees are still returned (with count of 0):

SELECT
    division,
    COUNT (first_name) as employee_count
FROM departments d 
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY division

CodePudding user response：

SELECT d.division, COUNT (e.first_name) 
 FROM departments d 
 JOIN employees e on d.id=e.department_id
GROUP BY d.division

This construction FROM departments d, employees is a "cross-join" between two tables

CodePudding user response：

Where clause is missing, put the relation between department and employee in the where clause