CODEWITHSUNDEEP
mysqljoinaverage
Alan A
Alan A

Reputation: 2551

MySQL join with AVG calculation

Is this possible?

I have a table which stores articles

I have another table which stores users ratings of the articles, each row is a rating which references the article

I want to get the average rating score for articles in the past 30 days:

SELECT `ex`.`article_id` , `ex`.`date` , `ex`.`title` , `r`.AVG(`rating`)
FROM `exclusive` `ex`
JOIN `ratings` `r` ON `ex`.`article_id` = `r`.`article_id`
WHERE `ex`.`date` > NOW( ) - INTERVAL 30 DAY

As you can see I'm trying to reference the 'rating' with the AVG function which is causing the issue. I think the issue is that the rating needs to be calculated before the select is made some I'm beginning to doubt if it's possible?

Upvotes: 0

Views: 70

Answers (3)

Ryan
Ryan

Reputation: 28187

You have to indicate how the data should be grouped, to indicate which groups to use for the average calculation, e.g.:

SELECT `ex`.`article_id` , `ex`.`title` , AVG(r.rating)
FROM `exclusive` `ex`
JOIN `ratings` `r` ON `ex`.`article_id` = `r`.`article_id`
WHERE `ex`.`date` > NOW( ) - INTERVAL 30 DAY
GROUP BY ex.article_id

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

Your syntax is a little off:

Change

..., r.AVG(rating)

To

..., AVG(r.rating)

And add a group by clause at the end of your query:

...
GROUP BY 1, 2, 3

It should look like:

SELECT `ex`.`article_id` , `ex`.`date` , `ex`.`title` , AVG(`r`.`rating`)
FROM `exclusive` `ex`
JOIN `ratings` `r` ON `ex`.`article_id` = `r`.`article_id`
WHERE `ex`.`date` > NOW( ) - INTERVAL 30 DAY
GROUP BY 1, 2, 3

Upvotes: 0

digitai
digitai

Reputation: 1842

Keep in mind that in order to average the rating you should aggregate itś dimensions, mainly by date, say grouping the last 30 days as you request, but to do so you should avoid aggregating by each review (title) and each date. Try this:

    SELECT `ex`.`article_id` , AVG( case when `ex`.`date` ` > NOW( ) - INTERVAL 30 DAY    
    then `r`.`rating` else 0 end) as 30_day_rating_average  FROM `exclusive` `ex` JOIN `ratings` `r` ON `ex`.`article_id` = `r`.`article_id` group by 1

You can also get a column with article name, instead of just article id.

Upvotes: 0

Related Questions