Single MySQL query with count and sum

Question

Hi there I have an SQL table that looks like this:

CREATE TABLE IF NOT EXISTS `designerswave_article_visited` (
  `article_visited_article_id` smallint(5) NOT NULL,
  `article_visited_user_id` smallint(5) NOT NULL,
  `article_user_rating` tinyint(1) DEFAULT NULL,
  UNIQUE KEY `article_id` (`article_visited_article_id`,`article_visited_user_id`)
)

And a query that looks like this:

SELECT SUM(article_user_rating) AS total_rating, COUNT(article_visited_user_id) AS hits
FROM article_visited
WHERE article_visited_article_id =1
GROUP BY article_visited_user_id

My problem is the rating could be 0 or NULL if the user hasn't rated the article. However in order get the average ratings I need to sum all the ratings that aren't 0 or NULL and divide by the total number of ratings.

I could do this quite easily with two queries using a where clause. But i'm interested to know if I could do it in a single query. Such as a where "article_user_rating > 0" within the SUM(). I'm sure i've seen something similar done before, but I can't find any documentation on it.

Answer 1

You can extend the where clause, but as Chad said, I'm not sure if this is really what you want:

SELECT SUM(article_user_rating) AS total_rating, COUNT(article_visited_user_id) AS hits
FROM article_visited
WHERE article_visited_article_id =1 and article_user_rating > 0
GROUP BY article_visited_user_id

Answer 2

As you can read here, in MySQL "group functions ignore NULL values". Then the null values can be ignored.

For the SUM aggregate function, the 0 value just can be ignored because X+0=X.