How to find most recurring day from TO_CHAR. ORACLE SQL-CodePudding

I'm trying to find the most recurring day in a Column. The table has Timestamps for the date. I can get a list of days:

SELECT TO_CHAR(column_name, 'DY') "Day" from Table;

Which returns a list of the days. Tue Tue Wed Tue Thu

How do I count those days and return the most frequently occurring day (TUE). I've tried so many different ways.

CodePudding user response：

This is based on sample Scott's EMP table.

Days when people were hired:

SQL> select hiredate, to_char(hiredate, 'DY') as day
  2  from emp
  3  order by day;

HIREDATE   DAY
---------- ---
01.05.1981 FRI
20.02.1981 FRI
28.09.1981 MON
23.01.1982 SAT
22.02.1981 SUN
02.04.1981 THU
03.12.1981 THU
03.12.1981 THU
09.12.1982 THU
08.09.1981 TUE
17.11.1981 TUE
09.06.1981 TUE
17.12.1980 WED
12.01.1983 WED

14 rows selected.

Which day is the most frequent? The one that has the most counts. Use RANK function to compute it (because two or more days could have had the same value).

SQL> with temp as
  2    (select to_char(hiredate, 'DY') as day,
  3            count(*) cnt,
  4            rank() over (order by count(*) desc) rnk
  5     from emp
  6     group by to_char(hiredate, 'DY')
  7    )
  8  select day, cnt
  9  from temp
 10  where rnk = 1;

DAY        CNT
--- ----------
THU          4

SQL>

CodePudding user response：

You can order by the count for each day and then use FETCH FIRST ROW WITH TIES to get the day(s) with the greatest frequency:

SELECT TO_CHAR(hiredate, 'DY') "Day"
FROM   emp
GROUP BY TO_CHAR(hiredate, 'DY')
ORDER BY COUNT(*) DESC
FETCH FIRST ROW WITH TIES;

Which, for the SCOTT schema outputs:

Day

TUE

THU

Day
TUE
THU

db<>fiddle here