sql query to display dept name,location name,number of employees

Here is my code:

SELECT dname,loc,avg(sal)
FROM dept,emp
GROUP BY loc;

I want to write a query to display the department name,location, and number of employees and the average salary for all the employees in that department. Label the columns dname,loc,number of employees and Avgsalary.

The data is:

DNAME          LOC           Number of People     Salary                     开发者_如何学JAVA   
-------------- ------------- ---------------- ----------                        
SALES          CHICAGO                      6    1566.67                        
RESEARCH       DALLAS                       5       2175                        
ACCOUNTING     NEW YORK                     3    2916.67              

--No need SUM(1) use COUNT(e.deptno) for number of people--

select d.dname as DNAME, d.loc as LOC, count(e.deptno)as "Number of people", round(avg(e.sal),2) as "Salary" from dept d, emp e where d.deptno = e.deptno group by d.dname, d.loc, e.deptno;

You are just missing out SUM(1) in your select list, and your group by was probably wrong for that question:

select dname as DNAME, loc as LOC, SUM(1) as Number_of_People, avg(sal) as AvgSalary from dept,emp group by dname, loc;