CODEWITHSUNDEEP
phpmysqlsqlaverage
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

MySQL / PHP Average Column Value with specific Parameters

I have a surprisingly basic question; I thought the answer was going to be more straightforward regarding Averaging columns. Here is my table with sample values:

Table: ratings

id, rating, item_id

Sample data

1, 5, 3
2, 1, 2
3, 2, 3
4, 4, 4 
5, 1, 2

What if I want to get the average rating of the item_id's marked "3"

The real answer would be 2.5 since there is a 3 with a1, and another with a 5.

Here is the query I believe:

 SELECT avg(rating) as average FROM ratings;

How do I specify ONLY the ratings with an item_id of 3? Do I use WHERE? GROUP BY? something else?

And the second part of the question: How do I output this into a single variable?

Upvotes: 1

Views: 2547

Answers (3)

Marin
Marin

Reputation: 1

You may want to check the algorithm I've been using in most of my projects. It doesn't require any complex MySQL statements and functions, and at the same time both the retrieval and update of the average rating value affect just a single row, even if you're dealing with millions of ratings. Everything is based on pure math :)

Check it out, hope it will be useful: Calculating Average Rating the Right Way using PHP and MySQL

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272066

You simply need to specify a WHERE clause:

SELECT AVG(rating) AS average
FROM ratings
WHERE item_id = 3
-- returns 1 row

If you want to get averages of entire data grouped by item_id you can use GROUP BY:

SELECT item_id, AVG(rating) AS average
FROM ratings
GROUP BY item_id
-- returns as many rows as the number of distinct item_ids

Upvotes: 4

RandomSeed
RandomSeed

Reputation: 29759

Well... have you tried?

SELECT AVG(rating) FROM ratings WHERE item_id = 3;

If your rating collumn is of an integer type, then you may want to do this instead:

SELECT AVG( CAST(rating AS FLOAT) ) FROM ratings WHERE item_id = 3;

And if you want to save the result in a variable:

SELECT AVG( CAST(rating AS FLOAT) ) INTO @var FROM ratings WHERE item_id = 3;
SELECT @var;

Upvotes: 2

Related Questions