CODEWITHSUNDEEP
sql
rohit
rohit

Reputation: 19

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                        
-------------- ------------- ---------------- ----------                        
SALES          CHICAGO                      6    1566.67                        
RESEARCH       DALLAS                       5       2175                        
ACCOUNTING     NEW YORK                     3    2916.67

Upvotes: 0

Views: 24983

Answers (2)

Iulian Stefan
Iulian Stefan

Reputation: 11

--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;

Upvotes: 1

Matt Fellows
Matt Fellows

Reputation: 6532

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;

Upvotes: 0

Related Questions