CODEWITHSUNDEEP
sql
Rahul Singh
Rahul Singh

Reputation: 31

How to use count and order by with inner join in mysql?

employee table

department table

How to display department name and number of employees working for that department.

My SQL code:

SELECT department.dname , employee.count(*)
FROM employee
INNER JOIN department
ON dno=dnumber
ORDER BY department.dname;

Upvotes: 0

Views: 247

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

The correct syntax is:

select d.dname, count(*)
from employee e inner join
     department d
     on d.dno = e.dnumber
group by d.dname
order by d.dname;

Notes:

  • You are describing an aggregation query with one row per department. That suggests GROUP BY.
  • Table aliases make the query easier to write and to read.
  • Qualify all column names. I have to guess what table dno comes from. There should be no reason to guess.

Upvotes: 1

Related Questions