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.

I'm confused, you're selecting a particular article_visited_article_id (1), and grouping by the article_visited_user_id, but the combination of those two columns is a unique key on the table, so that means you'll only ever have one row come up for each user. The GROUP BY will never group any rows. What exactly are you trying to select? The average rating of a particular article? — Chad Birch
– Chad Birch, Commented Mar 10, 2010 at 0:12

David Kanarek · Accepted Answer · 2010-03-10 00:19:11Z

3

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

answered Mar 10, 2010 at 0:19

David Kanarek

12.6k5 gold badges49 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FerranB · Accepted Answer · 2010-03-10 00:26:10Z

0

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.

answered Mar 10, 2010 at 0:26

FerranB

37k18 gold badges70 silver badges87 bronze badges

Collectives™ on Stack Overflow

Single MySQL query with count and sum

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related