Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: group by help!

  1. #1
    Join Date
    Apr 2005
    Posts
    10

    Unanswered: 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.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    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.

  3. #3
    Join Date
    Apr 2005
    Posts
    10

    re: GROUP BY help!

    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2005
    Posts
    10
    But we can not use JOIN. The only SQL constructS professor used are SELECT, FROM, WHERE, and GROUP BY.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    FYI the query you posted in #1 is a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2005
    Posts
    10
    OK, then show me your SQL query for this problem. THIS IS NOT A HOMEWORK QUESTION.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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?

  10. #10
    Join Date
    Apr 2005
    Posts
    10
    I did not try to write a query by myself using the hint Rudy gave me in post #4?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    Code:
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2005
    Posts
    10
    Quote Originally Posted by r937
    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?
    Code:
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, then do it

    good luck with your assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Without neither UNION nor JOIN:
    Code:
    select dnumber,
           (select count(*) from employee where dno=dnumber)
    from department
    Even without GROUP BY !
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Mar 2006
    Posts
    56
    I used Peter's coding to retrieve only those departments without employees.
    Code:
    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.
    Code:
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •