SQL COUNT () function and LEFT OUTER JOIN
I have two tables, users and departments. I want to have a table where there are two columns: first, the name of the department, the second is the number - how many users are assigned to this department. And I have this piece of code:
SELECT department_name as 'deptName',
COUNT(users.department_id) as 'userCount'
FROM departments
LEFT OUTER JOIN users
ON departments.id = users.department_id
GROUP BY 'deptName'
Department column columns:
integer id PK
varchar(20) department_name
Custom table columns:
integer id PK
varchar(20) name
varchar(20) surname
int department_id FK
But that won't work.
Now I have 2 departments, and the output should be two lines, first with the number 8 and the second with the counter 1. But I only see one line with the entire count (9). I am using MySQL installed with XAMPP.
source to share
SELECT department_name as 'deptName',
COUNT(users.department_id) as 'userCount'
FROM departments
LEFT OUTER JOIN users
ON departments.id = users.department_id
GROUP BY `deptName`
Notice the label marks against your single quotes in the GROUP BY (that's the key to the left of the 1 on your keyboard). Refer to: http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html
You can also just group by section name (the field itself, not the alias)
You are currently grouping the literal value "deptName", not the field where you specified the alias deptName, so you only have 1 line. You don't actually do any grouping.
source to share