group by help!

I have 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 |
+----+-----+

Although department 2 has no employee. It should show up on the result with the value of count(*) to be 0 according my professor. But it did not show up. I tested the above query in MySQL. Do I need to config something in MySQL in order to let department 2 to show up. Or this is the universal result I will get no matter whether I use MySQL, Oracal, or Microsoft Access. Thanks.You did an inner join to employee. Your query asked to count how many employees were in departments that had employees. You will have to do a query that will list all departments and then go find how many employees in each. Since this is for homework I will just provide a suggestion or suffer the wrath of others. A sum on a case statement would work for this or you could union the departments with no employees to the departments with employees.|||No, this is not for homework. This is a sample in lecture slide. I swell. The original problem statement 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".

Professor tried to show that by using SELECT, FROM, WHERE, and GROUP BY alone, the above problem could be solved. But I tested his query (see the first post), the result only contains the number of employees for the departments which have employees. The results does not contain the number of employees for the departments which do not have employees.

See if you can come out a SQL query for above problem. The SQL query should contain SELECT, FROM, WHERE, and GROUP BY only. Or you tell me this is impossible.|||of course it's possible

hint: use either a LEFT OUTER JOIN or RIGHT OUTER JOIN

(you can look up which one would be appropriate in your situation)|||But we can not use JOIN. The only SQL constructS professor used are SELECT, FROM, WHERE, and GROUP BY.|||if you cannot use JOIN, use a subquery

by the way, in the real world, "cannot use JOIN" is a bogus requirement

that's what's wrong with homework assigments, often they are unrealistic|||FYI the query you posted in #1 is a join|||OK, then show me your SQL query for this problem. THIS IS NOT A HOMEWORK QUESTION.|||Did you try to write a query by yourself using the hing Rudy gave you in post #4? If so, how does it look like and what did you get as a result?|||I did not try to write a query by myself using the hint Rudy gave me in post #4?|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)

Yes, it is working. But can you remove UNION from query? With UNION, I can come out my own SQL query without any help.|||okay, then do it

good luck with your assignment|||Without neither UNION nor JOIN:select dnumber,
(select count(*) from employee where dno=dnumber)
from departmentEven without GROUP BY !|||I used Peter's coding to retrieve only those departments without employees.
select dnumber,
(select count(*) from employee where dno=dnumber) counter
from department
where counter = 0

The database returns an Oracle error. ORA-00904: "COUNTER": invalid identifier. However, when the WHERE keyword is followed by the full code of the made-up column, the SQL is executed okay.
select dnumber,
(select count(*) from employee where dno=dnumber)
from department
where (select count(*) from employee where dno=dnumber) = 0
This above is okay. Very curious too me. Any comments?|||Ikviens, what are you trying to do? departments without employees? use a LEFT OUTER JOIN with an IS NULL check|||The explanation is simple:
the order of interpretation of an SQL query is as follows:
FROM
WHERE
GROUP BY
HAVING
SELECT
[UNION]
[ORDER BY]

Hence, an alias defined in the SELECT subclause is not available in the other subclauses (except in the ORDER BY subclause).

You may however write what you want as follows:SELECT *
FROM (select dnumber,
(select count(*) from employee where dno=dnumber) counter
from department) T
WHERE counter = 0since now the alias is visible in the outer FROM subclause and hence available in the outer WHERE subclause.|||peter, that was an excellent explanation, excellent

:)|||A SQL statement is interpreted in an order and, in that order, SELECT clause is placed after FROM, WHERE, GROUP BY and HAVING clauses.
Two magic facts I have never been aware of. I feel my cognizance is widened so much. Thank you Peter!|||Without neither UNION nor JOIN:select dnumber,
(select count(*) from employee where dno=dnumber)
from departmentEven without GROUP BY !

Now, UNION was gone. It is good. But if you use GROUP BY, it will make the SQL query more readable. See if you can come out the SQL query by using SELECT, FROM, WHERE, and GROUP BY.|||no, wingstech, let's see you do it

please, let's see your solution, we've given you lots of options, and you don't seem to be satisfied with any of them, so let's see yours

please|||See if you can come out the SQL query by using SELECT, FROM, WHERE, and GROUP BY.select dnumber,
(select count(*) from employee where dno=dnumber)
from department
group by dnumber|||Let me see.|||no, wingstech, let's see you do it

please, let's see your solution, we've given you lots of options, and you don't seem to be satisfied with any of them, so let's see yours

please|||select dnumber,
(select count(*) from employee where dno=dnumber)
from department
group by dnumber

What a wonderful solution! See if you can remove the embedded SQL query from SELECT clause. All SQL queries I saw in professor's lecture slides do not have embedded SQL query in SELECT clause. I saw him using embedded SQL query in WHERE clause.|||What a wonderful solution! it's not that wonderful -- for one thing, the GROUP BY is superfluous!!|||it's not that wonderful -- for one thing, the GROUP BY is superfluous!!

But if professor request you to use it? e.g. in exam|||as i said earlier in this very same thread, that would be bogus

any professor that says you should use GROUP BY when you don't need to is ... well, let's just say i don't expect this to ever happen in the real world

please, why don't you just use the LEFT OUTER JOIN as i suggested earlier, and let's move on to some other problem|||to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
select dnumber, sum(emps) as employees
from (
select dnumber, 0 as emps
from department
union all
select dno, count(*)
from employee
group by dno
)
group by dnumber look ma, no WHERE clause :) :)Rudy - did you not fall on the "a UNION is a join" side of the fence in a discussion on these hallowed boards some time ago? I imagine that would be the sort of analysis a dedicated student (such as young wingstech here) would lap up.

Assuming the professor did stipulate that a FROM clause should be used without a JOIN then it was a rather fiendish challenge (assuming CROSS joins are counted and the UNION(esque) operators are not).|||pootle, you Audacious Flump Cuddler you, your kind words do not go unappreciated

if you would kindly please get in touch with me via my contact page or email address thereon, i want to ask you a private question

a UNION is a "join" only in the most generous interpretations, which, i hasten to add, should always be tried -- it is helpful to realize that to a newbie, the distinctions between a cross join and a join and a union are difficult to master

as for this thread, i trust that my aggravation has managed to peek through my usual carefully neutral responses, so i shall just leave it at that

:)|||I think you are concious of the esteem in which I hold you rudeboy :)

It is laudable that wingstech has had the wherewithal to follow up a slide he* was presented with in a lecture. It is equally a shame that he* has not had the courage to attempt a solution of his* own. That is all that you and Peter are asking of him*. Go on wingstech - have a stab at it!

replace with she\ her if appropriate.|||outer queries shud be used for this purpose