I have an sql question. I have two

[jscorpion]

I have an sql question.
I have two tables
one has 'deptno' and 'dname' in it
two has 'empno' 'deptno' and 'sal' in it
I would like to display deptname, number of employees and average sal in each dept.

so the display would look like this

dname number of people sal
accounting 3 29000
research 6 42000

I can't seem to join them together I keep getting grouping errors. Can anyone help?

 

[SantaMufasa]

JScorpion,

Here is some code for you (which also relies upon some SQL*Plus formatting:

col name heading "Department|Name" format a15
col a heading "Number|of|People" format 999,999
col b heading "Average|Salary" format 999,999.99

select name, count(*) a, avg(salary) b
from s_dept, s_emp
where s_dept.id = s_emp.dept_id
group by name;

                  Number
Department            of     Average
Name              People      Salary
--------------- -------- -----------
Administration         2    6,330.00
Finance                1    1,450.00
Operations            15    1,086.80
Sales                  7    1,367.86

4 rows selected.

Dave
Sandy, Utah, USA @ 01:48 GMT, 18:48 Mountain Time

 

[jscorpion]

thanks for the post SantaMufasa, but I had already figured the problem out. It is very similar to your solution though I did have a few extra columns that I did not mention. Here is the final sql statement I used.

SELECT DEPT.DNAME, DEPT.LOC, COUNT(EMP.DEPTNO) "Number of People",
TO_CHAR(ROUND(AVG(EMP.SAL),2),'999999.00') " Salary"
FROM DEPT, EMP
WHERE DEPT.DEPTNO(+) = EMP.DEPTNO
GROUP BY DEPT.DNAME, DEPT.LOC;

thanks again.