Find day of week with most hires-CodePudding

I am trying to find the employee hire_date (day of week) where the most employees were hired. In my test CASE below the answer should be Tuesday.

As you can see I can list all the days but I'm having a problem narrowing down the result to 1 row.

Any help would be greatly appreciated. I listed my failed attempt. If there is a more efficient way to rewrite the query I would prefer any input.


CREATE TABLE employees (employee_id, first_name, last_name, hire_date) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03' FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04' FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05' FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06' FROM DUAL UNION ALL
SELECT 5, 'Nancy', 'Turner', DATE '2001-04-07' FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08' FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Gold', DATE '2001-04-10' FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11' FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17' FROM DUAL;


SELECT TO_CHAR(HIRE_DATE,'DAY') DAY, count(*) cnt FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY') 

DAY                CNT
TUESDAY      3
FRIDAY       1
SUNDAY       1
SATURDAY     1
WEDNESDAY    2
THURSDAY     1

/* not working */

SELECT  e.*
FROM EMPLOYEES e
INNER JOIN
    (SELECT employee_id, TO_CHAR(HIRE_DATE,'DAY') DAY
    FROM EMPLOYEES
    GROUP BY TO_CHAR(HIRE_DATE,'DAY')
    HAVING COUNT(1)=(SELECT MAX(COUNT(1))FROM EMPLOYEES GROUP BY TO_CHAR(HIRE_DATE,'DAY'))) AS empdays
    ON TO_CHAR(e.HIRE_DATE, 'DAY') = empdays.DAY;

CodePudding user response：

You neither need a subquery or nor a join to use if the DB's version is 12c , but just use the FETCH clause following ORDER BY in order to sort the counts descendingly such as

SELECT TO_CHAR(hire_date, 'DAY') AS day, COUNT(*) AS cnt
  FROM employees
 GROUP BY TO_CHAR(hire_date, 'DAY')
 ORDER BY cnt DESC
 FETCH FIRST 1 ROW WITH TIES

CodePudding user response：

This works for me:

select DAY, cnt
 from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY
             ,count(*) cnt
         FROM EMPLOYEES
        GROUP BY TO_CHAR(HIRE_DATE,'DAY'))
        where cnt = (select max(cnt)
                       from (SELECT TO_CHAR(HIRE_DATE,'DAY') DAY
                                   ,count(*) cnt
                               FROM EMPLOYEES
                              GROUP BY TO_CHAR(HIRE_DATE,'DAY')))

Produces following results

DAY	CNT
Tuesday	3

Refer to this db<>fiddle