GROUP BY hepe!

[wingstech]

I have a problem which is "For each department, find the department number and the number of employees of the department. It does not matter whether the department has employee or not"

I wrote the following SQL query:

SELECT DEPARTMENT.dnumber, COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dno = DEPARTMENT.dnumber
GROUP BY DEPARTMENT.dnumber

I had tested this query on the following table instances:

department
+--------------+---------+------------+-------------+
| dname | dnumber | mgrssn | mgrstartdate |
+--------------+---------+------------+-------------+
| R & D | 5 | 333445555 | 1988-05-22 |
| Administration | 4 | 987654321 | 1995-01-01 |
| Headquarters | 1 | 888665555 | 1981-06-19 |
| security | 2 | 123456789 | 1990-07-15 |
+-------------+----------+-----------+--------------+

employee
+------------+-----+------------+----------------+-----------------+-------------------------------------+------+----------+-----------------+-----+
| fname | mint | lname | ssn | bdate | address | sex | salary | superssn | dno |
+------------+-----+------------+----------------+-----------------+-------------------------------------+------+----------+-----------------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 231 Fondren, Houston, TX | M | 30000 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1965-12-18 | 638 Voss, Houston, TX | M | 40000 | 888665555 | 5 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321 Castle, Spring, TX | F | 25000 | 987654321 | 4 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX | F | 43000 | 888665555 | 4 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX | F | 25000 | 333445555 | 5 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 960 Dalls, Houston, TX | M | 25000 | 987654321 | 4 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX | M | 55000 | NULL | 1 |
| Larry | W | Clinton | 777225555 | 1972-03-15 | 100 Main, Houston, TX | M | 50000 | 888665555 | 1 |
+------------+------+-----------+-----------------+----------------+--------------------------------------+------+----------+----------------+-----+
(Note: the field dno is the department number of department the employee woks for)


I got following result:
+---------+-----------------+
| dnumber | COUNT(*) |
+------------+--------------+
| 1 | 2 |
| 4 | 3 |
| 5 | 4 |
+---------+-----------------+

The results I got did not contatin department 2 which has no employees. So these results are incomplete. Can you come out a SQL query to let deparment 2 show up with the value of count(*) to be 0? You can only use SELECT, FROM, WHERE, and GROUP BY in query.

[Nakirema]

SELECT department.dnumber, COUNT(*)
FROM employee,
LEFT JOIN department
ON employee.dno = department.dnumber
GROUP BY department.dnumber

Try this...

Just a suggestion, you should get in a habit of naming your keys the same...
like dno and dnumber... they should both be dno.. it's easier to find it that way.. also, think about naming your tables like this ..... tbl_employee and tbl_department.. it lets you see what is a table and what is not..

I'm not sure about that COUNT()... I think you may need to do i like this..
COUNT(*) AS NUM

[wingstech]

It does not work. The deparment 2 still not show up in the results.

[Nakirema]

SELECT department.dnumber, COUNT(*)
FROM department,
LEFT JOIN employee
ON employee.dno = department.dnumber
GROUP BY department.dnumber

Try this...

I messed up on the first one..

[sacx13]

Check IS_NULL SQL function...

[wingstech]

SELECT department.dnumber, COUNT(*)
FROM department,
LEFT JOIN employee
ON employee.dno = department.dnumber
GROUP BY department.dnumber

Try this...

I messed up on the first one..

Thanks. It still does not work. The value of COUNT(*) for department 2 should be 0. But it is 1 in results. Can you fix this error? The followings are results.

+---------+----------+
| dnumber | COUNT(*) |
+---------+----------+
| 1 | 2 |
| 2 | 1 |
| 4 | 3 |
| 5 | 4 |
+---------+----------+