mysql - Sql conditional count with join -

i cannot find answer problem here on stackoverflow. have query spans 3 tables:

newsitem +------+----------+----------+----------+--------+----------+ | guid | supplier | lastedit | showdate |  title | contents | +------+----------+----------+----------+--------+----------+ newsrating +----+----------+--------+--------+ | id | newsguid | userid | rating | +----+----------+--------+--------+ usernews +----+----------+--------+----------+ | id | newsguid | userid | readdate | +----+----------+--------+----------+ 

newsitem contains newsitems, newsrating contains ratings users give newsitems, , usernews contains date when user has read newsitem.

in query want every newsitem, including number of ratings newsitem , average rating, , how many times newsitem has been read current user.

what have far is:

select newsitem.guid, supplier, count(newsrating.id) numberofratings,     avg(newsrating.rating) rating,     count(case usernews.userid when 3 1 else null end) numberofreads newsitem     left join newsrating on newsitem.guid = newsrating.newsguid     left join usernews on newsitem.guid = usernews.newsguid     group newsitem.guid 

i have created sql fiddle here: http://sqlfiddle.com/#!9/c8add/8

both count() calls don't return numbers want. numberofratings should return total number of ratings newsitem (by users). numberofreads should return number of reads for current user newsitem.

so, newsitem guid d104c330-c319-40e8-8be3-a7c4f549d35c should have 2 ratings , 3 reads current user userid = 3.

i have tried conditional counts , sums, no success yet. how can accomplished?

the main problem see you're joining in both tables together, means you're going multiplying out both numbers, why counts aren't going correct. example, if newsitem has been read 3 times user , rated 8 users you're going end getting 24 rows, has been rated 24 times. can add distinct count of ratings ids , should correct issue. average should unaffected because average of 1 , 2 same average of 1, 1, 2, & 2 (for example).

you can handle reads adding userid join condition (since it's outer join shouldn't cause loss of results) instead of in case statement count, can count on distinct id values usernews. resulting query be:

select     i.guid,     i.supplier,     count(distinct r.id) number_of_ratings,     avg(r.rating) avg_rating,     count(distinct un.id) number_of_reads     newsitem left outer join newsrating r on r.newsguid = i.guid left outer join usernews un on     un.newsguid = i.guid ,     un.userid = @userid group     i.guid,     i.supplier 

while should work, might better results subquery, above needs explode out results , aggregate them, perhaps unnecessarily. also, people might find below little clearer.

select     i.guid,     i.supplier,     r.number_of_ratings,     r.avg_rating,     count(*) number_of_reads     newsitem left outer join (     select         newsguid,         count(*) number_of_ratings,         avg(rating) avg_rating             newsrating     group         newsguid ) r on r.newsguid = i.guid left outer join usernews un on un.newsguid = i.guid , un.userid = @userid group     i.guid,     i.supplier,     r.number_of_ratings,     r.avg_rating