CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Tim
Tim

Reputation: 101

Counting the Maximum Number of Values

I have a data table Employees, I want to show all the employees who have been hired on the same day on which the maximum number of employees has been hired. Here's my code:

select last_name, hire_date, count(*) as "Number of Employees Hired" 
from employees
group by last_name, hire_date
where hire_date = max(count(*));

However, this code shows "SQL Command Not Properly Ended" error when I run it. How do I fix it so that it runs correctly?

Upvotes: 0

Views: 42

Answers (2)

OldProgrammer
OldProgrammer

Reputation: 12169

This may not be the cleanest way, but I think will do the trick:

SELECT last_name,
  max_hire.hire_date,
  max_hire.cnt
FROM
  (SELECT *
  FROM
    (SELECT hire_date,
      COUNT(*) cnt
    FROM employees
    GROUP BY hire_date
    ORDER BY COUNT(*) DESC
    )
  WHERE rownum = 1
  ) max_hire
INNER JOIN employees
ON employees.hire_date = max_hire.hire_date

Upvotes: 1

Syed Shoaib Abidi
Syed Shoaib Abidi

Reputation: 2366

Firstly, you should use WHERE before the GROUP BY clause and secondly you should use the aggregate function (count) correctly, as in your case count(*) cannot be compared within the WHERE clause.

You can go with the approach below without using where: (Tested under MySQL)

select last_name, hire_date, count(*) as "Number of Employees Hired" 
from employees
group by last_name, hire_date
Order by "Number of Employees Hired" desc limit 1

Hope this helps. Cheers.

Upvotes: 0

Related Questions