1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

GROUP BY hepe!

Discussion in 'Databases' started by wingstech, Apr 3, 2006.

  1. #1
    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.
     
    wingstech, Apr 3, 2006 IP
  2. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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
     
    Nakirema, Apr 3, 2006 IP
    jaymcc likes this.
  3. wingstech

    wingstech Guest

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It does not work. The deparment 2 still not show up in the results.
     
    wingstech, Apr 3, 2006 IP
  4. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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..
     
    Nakirema, Apr 3, 2006 IP
  5. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #5
    Check IS_NULL SQL function...
     
    sacx13, Apr 4, 2006 IP
  6. wingstech

    wingstech Guest

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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 |
    +---------+----------+
     
    wingstech, Apr 4, 2006 IP