Calculate Monthly Average With Multiple Records in a Month-CodePudding

I have a dataset with the structure below. I want to calculate a monthly average of the views.

I attempted to calculate the yearly frequency with the following code and I believe it is correct

SELECT 
EXTRACT (YEAR FROM TO_DATE("date",'Month YYYY') ) AS "year",
AVG("views")
FROM talks
GROUP BY EXTRACT (YEAR FROM TO_DATE("date",'Month YYYY') )
ORDER BY "year" DESC

When it comes to the monthly analysis I have the problem that there several records for the same month in a year and there several years with the same months (in reality the dataset has information for many years - this a reduced version).

How can I go to implement this?

CodePudding user response：

If you want the average per month then just group by your current date field. If you want the average per month regardless of year then you would have to extract the month part of the current date field and group by that.

But your date field now appears to be having string data type; it would be better to use proper date data type. Then your analysis would be much easier, more flexible, better performing.