A sample of data from my APPOINTMENT table

 APP_ID A_DATE_TI     PET_ID      VN_ID
---------- --------- ---------- ----------
         1 20-JAN-23      10001        801
         2 20-JAN-23      10002        803
         3 20-JAN-23      10003        804
         4 20-JAN-23      10004        803
         5 15-JAN-23      10005        801
         6 14-JAN-23      10006        803
         7 13-JAN-23      10007        804
         8 12-JAN-23      10008        803
         9 01-FEB-23      10009        801
        10 02-FEB-23      10010        803
        11 03-FEB-23      10011        804

SELECT 
APP_ID, 
TO_CHAR(A_DATE_TIME, 'DAY MONTH YYYY DD hh:mi') App_Date_Time, 
PET_ID, 
VN_ID
FROM APPOINTMENT

CodePudding user response：

SELECT TO_CHAR(a_date_time,'FMDay') day_of_week
      ,COUNT(*) number_of_appointments
  FROM appointment
 GROUP BY TO_CHAR(a_date_time,'FMDay')
 ORDER BY number_of_appointments ASC
 FETCH FIRST ROW WITH TIES

CodePudding user response：

You can determine the day of the week from your date column with

 TO_CHAR(date A_DATE_TI, 'DAY')

So, you can use this query to find how many appointments occur on each weekday.

SELECT COUNT(*) appointments, 
       TO_CHAR(A_DATE_TI, 'DAY') weekday
  FROM appointment
 GROUP BY TO_CHAR(A_DATE_TI, 'DAY')

And this finds the day with the most appointments.

SELECT COUNT(*) appointments, 
       TO_CHAR(A_DATE_TI, 'DAY') weekday
  FROM appointment
 GROUP BY TO_CHAR(A_DATE_TI, 'DAY')
 ORDER BY COUNT(*) DESC
 LIMIT 1

CodePudding user response：

You can generate a list of all the days of the week and then LEFT OUTER JOIN to your appointments table and then ORDER the rows by the number of appointments and, from Oracle 12, FETCH FIRST ROW WITH TIES to find the minimum(s):

WITH days_of_week (day) AS (
  SELECT TO_CHAR(TRUNC(SYSDATE, 'IW')   LEVEL - 1, 'DY')
  FROM   DUAL
  CONNECT BY LEVEL <= 7
)
SELECT d.day,
       COUNT(a.app_id) AS num_appointments
FROM   days_of_week d
       LEFT OUTER JOIN appointment a
       ON d.day = TO_CHAR(a.A_DATE_TI, 'DY')
GROUP BY d.day
ORDER BY
       num_appointments ASC
FETCH FIRST ROW WITH TIES;

Which, for the sample data:

CREATE TABLE appointment (APP_ID, A_DATE_TI, PET_ID, VN_ID) AS
SELECT  1, DATE '2023-01-20', 10001, 801 FROM DUAL UNION ALL
SELECT  2, DATE '2023-01-20', 10002, 803 FROM DUAL UNION ALL
SELECT  3, DATE '2023-01-20', 10003, 804 FROM DUAL UNION ALL
SELECT  4, DATE '2023-01-20', 10004, 803 FROM DUAL UNION ALL
SELECT  5, DATE '2023-01-15', 10005, 801 FROM DUAL UNION ALL
SELECT  6, DATE '2023-01-14', 10006, 803 FROM DUAL UNION ALL
SELECT  7, DATE '2023-01-13', 10007, 804 FROM DUAL UNION ALL
SELECT  8, DATE '2023-01-12', 10008, 803 FROM DUAL UNION ALL
SELECT  9, DATE '2023-02-01', 10009, 801 FROM DUAL UNION ALL
SELECT 10, DATE '2023-02-02', 10010, 803 FROM DUAL UNION ALL
SELECT 11, DATE '2023-02-03', 10011, 804 FROM DUAL;

Outputs:

fiddle