MySQL sum plus count in same query

Question

I'm trying to get the rates from anonymous people plus the ones who are registered. They are in different tables.

SELECT product.id, (SUM( users.rate + anonymous.rate ) / COUNT( users.rate + anonymous.rate ))
FROM products AS product
LEFT JOIN users ON users.id_product = product.id
LEFT JOIN anonymous ON anonymous.id_product = product.id
GROUP BY product.id
ORDER BY product.date DESC 

So, the tables are like the following:

users-->
id | rate | id_product | id_user
1     2        2           1
2     4        1           1
3     5        2           2

anonymous-->
id | rate | id_product | ip
1     2        2          192..etc
2     4        1          198..etc
3     5        2          201..etc

What I'm trying with my query is: for each product, I would like to have the average of rates. Currently the output is null, but I have values in both tables.

Thanks.

Answer 1

Try like this..

SELECT product.id, (SUM( ifnull(ur.rate,0) + ifnull(ar.rate,0) ) / (COUNT(ur.rate)+Count(ar.rate)))
FROM products AS product
LEFT JOIN users_rate AS ur ON ur.id_product = product.id
LEFT JOIN anonymous_rate AS ar ON ar.id_product = product.id
GROUP BY product.id 

Sql Fiddle Demo