I am trying to get the dname, loc, and count the ename's, plus I want to include the sal from the table. Can someone tell me what I am doing wrong.
Heres my statement with the error I get
SQL> select dname, loc, (select count(ename), sal from emp where DEPTNO = dept.deptno) as Number_of_people from dept;
select dname, loc, (select count(ename), sal from emp where DEPTNO = dept.deptno) as Number_of_people from dept
*
ERROR at line 1:
ORA-00913: too many values
SQL>
Heres my table
SQL> select empno, ename, job, hiredate, sal from emp;
EMPNO ENAME JOB HIREDATE SAL
---------- ---------- --------- --------- ----------
7839 KING PRESIDENT 17-NOV-81 5000
7698 BLAKE MANAGER 01-MAY-81 2850
7782 CLARK MANAGER 09-JUN-81 2450
7566 JONES MANAGER 02-APR-81 2975
7654 MARTIN SALESMAN 28-SEP-81 1250
7499 ALLEN SALESMAN 20-FEB-81 1600
7844 TURNER SALESMAN 08-SEP-81 1500
7900 JAMES CLERK 03-DEC-81 950
7521 WARD SALESMAN 22-FEB-81 1250
7902 FORD ANALYST 03-DEC-81 3000
7369 SMITH CLERK 17-DEC-80 800
EMPNO ENAME JOB HIREDATE SAL
---------- ---------- --------- --------- ----------
7788 SCOTT ANALYST 09-DEC-82 3000
7876 ADAMS CLERK 12-JAN-83 1100
7934 MILLER CLERK 23-JAN-82 1300
14 rows selected.
SQL>
Heres the second table
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
To get number of people and total Salary, try this...
select dept.dname, dept.loc,
count(emp.ename) as Number_of_people, sum(emp.sal) as Total_Salary
from emp
join dept on emp.DEPTNO = dept.deptno
group by dept.dname,dept.loc
You can get other things as well
select dept.dname, dept.loc,
count(emp.ename) as Number_of_people,
sum(emp.sal) as Total_Salary,
avg(emp.sal) as Average_salary,
min(emp.hiredate) as First_dept_hire,
max(emp.hireDate) as Last_dept_hire
from emp
join dept on emp.DEPTNO = dept.deptno
group by dept.dname,dept.loc