CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

Count the number of employees for supervisor

SELECT 
e.lname AS employee_name
, s.lname AS supervisor_name
, e.superssn AS supervisor_ssn
FROM employee e INNER JOIN employee s
WHERE e.superssn=s.ssn

I actually have two questions. The first one is, the above statement works just fine. However right now it will show bunch of employees with their supervisor. Not well organized. By the way there are three supervisors. Is there a way to show let's say supervisor A with employees A and supervisor B with employees B and so on?

The second problem is that I also tried to just count the number of employees for each supervisor rather than showing their name with COUNT(), I tried several different ones but non worked.

Upvotes: 0

Views: 2335

Answers (1)

Daniel Gimenez
Daniel Gimenez

Reputation: 20524

A few things

1) It is good practice to keep your join predicate with your join. So change:

FROM employee e INNER JOIN employee s WHERE e.superssn = s.ssn

To:

FROM employee e INNER JOIN employee s ON e.superssn = s.ssn

2) To keep them together by supervisor, just use an order by:

SELECT 
  e.lname AS employee_name
  , s.lname AS supervisor_name
  , e.superssn AS supervisor_ssn
FROM employee e INNER JOIN employee s ON e.superssn = s.ssn
ORDER BY s.lname

3) To do a count of employees for each supervisor use a group by and the COUNT aggregate function

SELECT 
  s.lname AS supervisor_name,
  COUNT(*) AS employee_count
FROM employee e INNER JOIN employee s ON e.superssn = s.ssn
GROUP BY s.lname
ORDER BY s.lname

Upvotes: 1

Related Questions