How to make an appointment to know the averages of people a day of a week

Question

Hello, I would like to know how to make a query (Query) in the Oracle database to know: What is the average number of guests per day of any week. Below is the bank script:

CREATE TABLE APP.HOSPEDAGENS (  
      hsp_id number(6),  
      hsp_checkin Date, 
      hsp_preco_total number(8,2),  
      hsp_checkout Date,  
      hsp_cli_id number(6),  
      hsp_qua_id number(6),  
      CONSTRAINT pk_hsp_id  PRIMARY KEY(hsp_id)  );  
ALTER TABLE APP.HOSPEDAGENS ADD FOREIGN KEY (hsp_cli_id) REFERENCES APP.CLIENTES (cli_id);  
ALTER TABLE APP.HOSPEDAGENS ADD FOREIGN KEY (hsp_qua_id) REFERENCES APP.QUARTOS (qua_id);

Answer 1

You can return the day of the week using the to_char function, formatting the date with the parameter’d', which returns the day of the week. Thus it is possible to group the query by the day of the week and count, ex:

select to_char(hsp_checkin, 'd') as DIA_DA_SEMANA, 
       count(*) as total 
  from HOSPEDAGENS 
 group by to_char(hsp_checkin, 'd')
 order by 1