So using the following 2 tables
Employee:
mysql> select * from employee;
+----------+-------+---------+-----------+------------+--------------------------+------+--------+-----------+-----+
| Fname | Minit | Lname | SSN | Bdate | Address | Sex | Salary | Superssn | Dno |
+----------+-------+---------+-----------+------------+--------------------------+------+--------+-----------+-----+
| John | B | Smith | 123456789 | 1955-01-09 | 731 Fondren, Cary, NC | M | 31500 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1945-12-08 | 638 Voss, Cary, NC | M | 42000 | 888665555 | 5 |
| Joyce | A | English | 453453453 | 1962-07-31 | 5631 Rice, Raleigh, NC | F | 26250 | 333445555 | 5 |
| Rameish | K | Naraya | 666884444 | 1952-09-15 | 975 Fire Oak, Angier, NC | M | 39900 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1927-11-10 | 450 Stone, Cary, NC | M | 55000 | NULL | 1 |
| Jennifer | S | Wallace | 987654321 | 1931-06-20 | 291 Berry, Garner, NC | F | 43000 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1959-03-29 | 980 Dallas, Cary, NC | M | 25000 | 987654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1958-07-19 | 3321 Castle, Apex, NC | F | 25000 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+--------------------------+------+--------+-----------+-----+
8 rows in set (0.01 sec)
Department:
mysql> select * from department
-> ;
+---------+----------------+-----------+
| dnumber | dname | mgrssn |
+---------+----------------+-----------+
| 1 | Headquarters | 888665555 |
| 2 | Development | NULL |
| 3 | Sales | NULL |
| 4 | Administration | 987654321 |
| 5 | Research | 333445555 |
+---------+----------------+-----------+
5 rows in set (0.00 sec)
I need to combine information using the having clause which department's employees have an average salary greater than 33000.
This is a copy of the output:
+--------------+-------------+
| dname | AVG(salary) |
+--------------+-------------+
| Headquarters | 55000 |
| Research | 35000 |
+--------------+-------------+
Ok where I am so far:
mysql> select d.dname, AVG(salary)
-> from department as d, employee as e
-> having avg(salary) > 33000
-> group by d.dname;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to
use near 'group by d.dname' at line 4
mysql> select d.dname, AVG(salary)
-> from department as d, employee as e
-> having avg(salary) > 33000
-> group by ????
What am I missing for the group by?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…