Showing colums and rounding 2 decmial places

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Showing colums and rounding 2 decmial places

Author

Topic

Maple50175
Starting Member

6 Posts

Posted - 2007-05-08 : 09:48:39

Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though

a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.

DNAME LOC Number of People Salary
-------------------- ---------------- --------------------- ----------
ACCOUNTING NEW YORK 3 2916.67
RESEARCH DALLAS 5 2175
SALES CHICAGO 6 1566.67

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-08 : 10:04:46

Depends on your structure but something like

select d.name, d.loc, nopeople = count(*), avsal = convert(decimal(9,2),average(e.salary))
from depts d
join employees e
on d.deptid = e.deptid
group by d.name, d.loc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pinar Uyanik
Starting Member

1 Post

Posted - 2008-08-03 : 22:08:25

Try this one, it look much easier;)

SELECT d.dname,
d.loc,
e.salary,
e.NumberOfPeople
FROM d
JOIN
(SELECT COUNT(ename) AS NumberOfPeople,
ROUND(AVG(sal),2) AS salary,
deptno
FROM e
GROUP BY deptno
) e
ON d.deptno = e.deptno;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources