How to find most recurring day from TO_CHAR. ORACLE SQL

February 14, 2022

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.

>Solution :

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>