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

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

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

CodePudding user response：

You don't need the window function:

• use the corresponding AVG aggregation function
• remove the score from the GROUP BY clause.

SELECT TO_CHAR(created_at, 'Mon YYYY') AS date,
       AVG(score) AS average_score
FROM   "reviews"
WHERE  "reviews"."score" IS NOT NULL
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.