How to find the Average of phone calls made per District for a specific month in SQL Query

Hi i need help in displaying a sql query

So i wanted to display the average of total number of calls made per district in a specific month, e.g january 2015, january 2016, january 2017, etc

Below is the sample database

id            created_on            district_name
11       January 1, 2014, 12:00 AM    azamgarh
24       January 1, 2014, 12:00 AM    badaun
7        January 1, 2014, 12:00 AM     badgam
1        January 1, 2014, 12:00 AM     bagalkot
6        January 1, 2014, 12:00 AM     baghpat
18       January 1, 2014, 12:00 AM    bahraich
4        January 1, 2014, 12:00 AM     balaghat

id is calls, created_on is date, district_name is district location

This is the my code regarding about this issue

 select
    t.district_name as "District",
    t.created_on::date as "Date",
    COUNT(t.id) AS "Total calls",
    AVG(COUNT(t.id)) OVER() as "Average"
from t
where
    date_part('month',  t.created_on::date) = 1 
    and date_part('year',  t.created_on::date) between 2013 and 2018
group by  
    date_part('year',  t.created_on::date)
    , date_part('month',  t.created_on::date)
    , district_name, created_on

this code only shows the total average of calls i have from 2010-2018 in the month of January instead of specific year of 2013 – 2018

Can someone help me regarding this problem? Thank you in advance

Answer

you can use date_part function to extract and group by month name and year.

select
    t.district_name as "District",
    t.created_on::date as "Date",
    SUM(t.id) / COUNT(t.id) as average
from t
where
    date_part('month',  t.created_on::date) = 1 
    and date_part('year',  t.created_on::date) <= 2018    
group by  
    date_part('year',  t.created_on::date)
    , date_part('month',  t.created_on::date)
    , district_name

Advertisement

Answer