SQL COUNT() function and LEFT OUTER JOIN
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 tick marks vs. your single quotes in the GROUP BY (this is 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 could also just group by department_name (the field itself, rather than the alias)
Currently you are grouping on the literal value 'deptName', not the field that you've given an alias of deptName, which is why you only have 1 row returned. You're not actually doing any grouping.
Radek Anuszewski
Software developer, frontend developer in AltConnect, mostly playing with AngularJS and, recently, BackboneJS / MarionetteJS. I have a blog on Medium.com, where I am writer in Frontend Weekly.
Updated on June 04, 2022Comments
-
Radek Anuszewski almost 2 years
I have two tables, users and departments. I want to have table, where are two columns: first is department name, second is count - 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's table columns are:
integer id PK varchar(20) department_name
User's table columns are:
integer id PK varchar(20) name varchar(20) surname int department_id FK
But it does not work.
Now I have 2 departments, and output should be 2 rows, first with count 8 and second with count 1. But I see only one row, with all count (9). I use MySQL installed with XAMPP.