CODEWITHSUNDEEP
sql
Ross O' Connell
Ross O' Connell

Reputation: 19

Display each department’s number and name and the number of employees employed in each department

This is what I tried and cant get it to work

SELECT d.deptno,d.dname
COUNT('e.empno') "number of employees"
FROM dept,emp
WHERE d.deptno = e.deptno
GROUP BY d.deptno,d.dname

any help is much appreciated thanks

Upvotes: 1

Views: 26795

Answers (4)

Marvin Aboagye
Marvin Aboagye

Reputation: 1

Try

SELECT d.department_id, 
       d.department_name, 
       COUNT(e.employee_id) as number_of_employees
FROM departments d 
JOIN employees e 
    on d.department_id = e.department_id 
GROUP BY d.department_id, d.department_name;

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

In sql-server it'll be like

SELECT d.deptno,d.dname,
COUNT(e.empno) as number_of_employees
FROM dept d,emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno,d.dname


the error occurs when you undefined the instance of the table used

just in case more optimized will be

SELECT d.deptno,d.dname, COUNT(e.empno) as number_of_employees FROM dept d join emp e on d.deptno = e.deptno GROUP BY d.deptno,d.dname

Upvotes: 1

Martao
Martao

Reputation: 865

Okay, you have some syntax errors:

  1. The COUNT(Something) is something to select, so it should be separated with a comma after "d.dname". Also, like Guillaume pointed out, it is a column and not a string, so no quotes are needed:
    SELECT d.deptno, d.dname, COUNT(e.empno)
  2. You use aliases like "d" and "e", but you did not define them. You would do this like:
    "FROM departments d"
  3. the join should be written as follows:
    "INNER JOIN employees e ON e.deptno = d.deptno
    (This is technically not a syntax error, but I advice to use inner join explicitly)
  4. The comments should be prefixed with '--'

P.S. Assuming T-SQL / MS SQL Server here.

Upvotes: 0

nbrooks
nbrooks

Reputation: 18233

It looks to me like you might be using Oracle, in which case the fix is fairly simple:

SELECT d.deptno, d.dname, count(e.empno) "number of employees"
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.deptno, d.dname

You need to explicitly tell the DB what the aliases are referring to. The error message likely mentioned an invalid/missing identifier 'd', meaning that it didn't know what that was.

Upvotes: 0

Related Questions