CODEWITHSUNDEEP
mysqlsql
frosty
frosty

Reputation: 2851

Count number of rows with specific values

I'm storing ratings for an item in a table called ratings.

value is an integer between 0 and 7 (the rating value).

As an example, let's say showcase_id = 1 has 10 total ratings:

Is there any efficient way I can select the total number of ratings for each specific value, the total number of ratings and the average rating from 1 single query?

e.g. the number of rows/count WHERE value = 6 is 2. Would I need to do 7 separate subqueries?

SELECT AVG(value),COUNT(*),????
FROM ratings
WHERE showcase_id = :showcase_id

Upvotes: 0

Views: 3943

Answers (3)

access_granted
access_granted

Reputation: 1927

What's wrong with group by?

SELECT AVG(value),COUNT(*), value
FROM ratings
WHERE showcase_id = :showcase_id
GROUP BY value;

EDIT (with the overall avg & totes):

select count(*) total_by_value, value, s1.full_avg, s1.full_total 
from ratings r,
(select avg(value) full_avg, count(*) full_total from ratings) s1
group by s1.full_avg, s1.full_total, value;

Upvotes: 0

Barmar
Barmar

Reputation: 781848

You can use the WITH ROLLUP modifier to GROUP BY to get the counts and average for each value, as well as the totals for everything (if you group by multiple columns it will also create subtotals for each inner group).

SELECT value, AVG(value) AS avg, COUNT(*) as count
FROM ratings
WHERE showcase_id = 1
GROUP BY value WITH ROLLUP

This will produce a row for each value with its count, and then a row with value = NULL for that aggregates the entire result.

DEMO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Are you referring to count(distinct)?

SELECT AVG(value), COUNT(*), COUNT(DISTINCT value)
FROM ratings
WHERE showcase_id = :showcase_id;

EDIT:

If you want the total for each value, you can stuff this into one column, using a subquery:

SELECT SUM(cnt * value) / SUM(cnt) as average,
       SUM(cnt) as total_cnt,
       GROUP_CONCAT(value, '-', cnt ORDER BY VALUE) as values
FROM (SELECT value, COUNT(*) as cnt
      FROM ratings r
      WHERE showcase_id = :showcase_id
      GROUP BY value
     ) r;

Perhaps the subquery meets your needs as well.

Upvotes: 0

Related Questions