CODEWITHSUNDEEP
sqldatabase
james2471993
james2471993

Reputation: 41

counting rows where column value match, over all distinct column values - sql

I'm using SQL and this is my goal:

Starting table:

anime_uid, reviewer, score

1, james, 8
1, john,  7
2, sam,   5
1, alice, 7
3, john,  5
2, alice, 4

My goal is to know how many times an anime was reviewed, and what is the avg score. meaning i need to have something like this:

anime_uid, times_reviewed, avg_score
1, 3, 7.33
2, 2, 4.5
3, 1, 5

I can't seem to deconstruct the query to sub-queries to complete this task, would love your help! Thanks!

Upvotes: 0

Views: 22

Answers (1)

sofos s
sofos s

Reputation: 79

you can try this query:

SELECT
    anime_uid,
    COUNT(DISTINCT reviewer) as times_reviewed,
    AVG(score) as avg_score
FROM table
GROUP BY anime_uid

Upvotes: 2

Related Questions