0

i've just study SQL for a month and there is many things I'm still cannot get a hold of. Can anyone help me, plz provide the results with some explaination, I need to understand it for future uses. I only list things that I cannot understand.

  • List the ID of the managers and the total number of employees reporting to each of them. Display the result in descending order of the total number of employees

I can do:

SELECT employeeNumber
FROM employees
WHERE jobTitle LIKE '%Manager%'
UNION
SELECT employeeNumber, COUNT(*)
FROM employees
WHERE reportsTo 'WHICH CONDITION?'
ORDER BY COUNT(*) DESC

Can someone fill in after 'reportTo', I cant find a condition that's working TYT

  • Display all offices and their counts in each country I think this mean showing a table with every country and count total number of offices in that country

I can do:

SELECT country, COUNT(*)
FROM offices
GROUP BY country
UNION
SELECT country, officeCode
FROM offices

But the results is not as expected

jarlh
  • 42,561
  • 8
  • 45
  • 63
Stellar
  • 1

1 Answers1

0
select
reportsTo,
COUNT(employeeNumber) as numberOfEmployees
from employees
group by reportsTo

Will give you a count of employeeNumbers that report to that reportsTo. This will not give you the managers where nobody is reporting to, so to do that you would have to make a JOIN:

SELECT
a.employeeNumber AS managerNumber,
COUNT(b.employeeNumber) AS numberOfEmployees
FROM employees AS a
LEFT JOIN employees AS b on (b.reportsTo=a.employeeNumber)
WHERE a.jobTitle LIKE '%Manager%'
GROUP BY a.employeeNumber
verhie
  • 1,298
  • 1
  • 7
  • 7