How to average a score in groups based on the month and year in SQL

Question

I have a table called reviews. Reviews has two columns I care about: score (int) and created_at (datetime). Imagine the following data

id	score	created_at
1	5	2022-01-15
2	1	2022-01-15
3	2	2022-02-01
4	3	2022-02-04
5	5	2022-03-30

I want to return the average score grouped by the month and the year. To get the following result

date	average_score
Jan 2022	3
Feb 2022	2.5
Mar 2022	5

My current code is below. It is almost working but it isn't grouping by the date and I can't figure out why. Please let me know if I am way off here but I think I am so close I just need the data to group

SELECT TO_CHAR(reviews.created_at, 'Mon YYYY') AS date,
       AVG(reviews.score) OVER (PARTITION BY TO_CHAR(reviews.created_at, 'Mon YYYY')) AS average_score
FROM   "reviews"
WHERE  "reviews"."score" IS NOT NULL
GROUP BY date, score

Answer 1

You don't need the window function:

• use the corresponding AVG aggregation function
• remove the score from the GROUP BY clause
• remove the null scores check, as the aggregation functions skip null values

SELECT TO_CHAR(created_at, 'Mon YYYY') AS date,
       AVG(score) AS average_score
FROM   "reviews"
GROUP BY date

Check the demo here.

Note: in your code the GROUP BY clause is doing nothing as long as there's no aggregation function applied to any field called in your SELECT clause.