CODEWITHSUNDEEP
oracle-databasesqlplus
NTesla
NTesla

Reputation: 69

pl sql query for average by month

It's for a school project. I got a table Consultation with the following data : 

DoctorId integer,
PatientFile varchar2(20),
visitDate date,
Diagnostic varchar2(20) and
Prescription varchar2(20).

I want to create a query that will show the average number of consultation by month. I try :

SELECT AVG(count(*)) AS count, MONTH(dateVisit) as month
FROM consultation
GROUP BY month

I doesn't work : I can't use the month fonction on dateVisit.

My questions : how would you do a query that will show the average number of consultation by month ?

Many thanks in advance for your help

Upvotes: 1

Views: 1014

Answers (1)

NTesla
NTesla

Reputation: 69

I found the solution : 

select avg (distinct (extract(month from visitDate))) as month from 
consultation;

So here's how it's working : 1- extract(month from table_name) as month from table_name. You can also put year or day instead of month. 2- distinct = will count the total for each month (instead of showing every record). 3- avg = average of each month.

Upvotes: 1

Related Questions