---

Examples of GROUP BY queries

---

• Example GROUP BY queries

  • Query 1:

    For each department, show the department number, number of employees and average salary paid to the employees in the department

  • Solution:

    select ssn, dno, salary from employee; +-----------+-----+----------+ | ssn | dno | salary | +-----------+-----+----------+ | 123456789 | 5 | 20000.00 | | 333445555 | 5 | 40000.00 | | 666884444 | 5 | 38000.00 | | 453453453 | 5 | 25000.00 | | 999887777 | 4 | 25000.00 | | 987654321 | 4 | 43000.00 | | 987987987 | 4 | 25000.00 | | 888665555 | 1 | 55000.00 | +-----------+-----+----------+ Group by dno and perform set function on each group !! Solution: SELECT dno, COUNT(ssn), AVG(salary) FROM employee GROUP BY dno DNO COUNT(SSN) AVG(SALARY) ---------- ---------- ----------- 1 1 55000 4 3 31000 5 4 33250

    ---

    ---

    ---

    ---

  • Query 2:

    For each department, show the department NAME, number of employees and average salary paid to the employees in the department

  • Solution:

    We need employee's information: ssn dno salary --------- ----------- --------- 123456789 5 20000.00 333445555 5 40000.00 666884444 5 38000.00 453453453 5 25000.00 999887777 4 25000.00 987654321 4 43000.00 987987987 4 25000.00 888665555 1 55000.00 We must include the department name first: select ssn, dno, dname, salary from employee, department where dno=dnumber +-----------+-----+----------------+----------+ | ssn | dno | dname | salary | +-----------+-----+----------------+----------+ | 123456789 | 5 | Research | 20000.00 | | 333445555 | 5 | Research | 40000.00 | | 666884444 | 5 | Research | 38000.00 | | 453453453 | 5 | Research | 25000.00 | | 999887777 | 4 | Administration | 25000.00 | | 987654321 | 4 | Administration | 43000.00 | | 987987987 | 4 | Administration | 25000.00 | | 888665555 | 1 | Headquarters | 55000.00 | +-----------+-----+----------------+----------+ Next: Form groups.... using WHAT attribute ??? WRONG: group by dno SELECT dname, COUNT(ssn), AVG(salary) FROM employee, department WHERE dno = dnumber GROUP BY dno SELECT list attribute must also be a grouping attribute !! CORRECT: SELECT dname, COUNT(ssn), AVG(salary) FROM employee, department WHERE dno = dnumber GROUP BY dname DNAME COUNT(SSN) AVG(SALARY) --------------- ---------- ----------- Administration 3 31000 Headquarters 1 55000 Research 4 33250

    (Note: some database systems - like MySQL - will not report the grouping attribute error)

    ---

    ---

    ---

    ---

  • Query 3:

    For each project, show: the project NAME, number of employees working on that project total number of hours worked by all employees.

  • Solution:

    We need to use the works_on information: essn pno hours --------- ----------- ------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 333445555 20 10.0 999887777 30 30.0 999887777 10 10.0 987654321 30 20.0 987654321 20 15.0 666884444 3 40.0 453453453 1 20.0 453453453 2 20.0 987987987 30 5.0 987987987 10 35.0 888665555 20 1.0 We first add in the pname info: select essn, pno, hours, pname from works_on, project where pno=pnumber essn pno hours pname --------- ----------- ------- --------------- 123456789 1 32.5 ProductX 123456789 2 7.5 ProductY 333445555 2 10.0 ProductY 333445555 3 10.0 ProductZ 333445555 10 10.0 Computerization 333445555 20 10.0 Reorganization 999887777 30 30.0 Newbenefits 999887777 10 10.0 Computerization 987654321 30 20.0 Newbenefits 987654321 20 15.0 Reorganization 666884444 3 40.0 ProductZ 453453453 1 20.0 ProductX 453453453 2 20.0 ProductY 987987987 30 5.0 Newbenefits 987987987 10 35.0 Computerization 888665555 20 1.0 Reorganization Form groups using pname: SELECT pname, COUNT(essn), SUM(hours) FROM works_on, project WHERE pno = pnumber GROUP BY pname PNAME COUNT(ESSN) SUM(HOURS) --------------- ----------- ---------- Computerization 3 55 Newbenefits 3 55 ProductX 2 52.5 ProductY 3 37.5 ProductZ 2 50 Reorganization 3 25