CODEWITHSUNDEEP
mysqljoin
Pavel Rogala
Pavel Rogala

Reputation: 163

Count by value in mysql with joins

I've got three tables: users, topics, and posts which are structured like this:

--users
----id
----name
----email
----password
----profile_pic

--topics
----id
----topic_title
----topic_category
----topic_content
----author_id

--comments
----id
----topic_id
----author_id
----comment
----mood

A sql fiddle of this is at: http://sqlfiddle.com/#!2/8e5241

Now what I need to do is to query all topics and get the author information of each plus the comment count on each topic. This was easy enough to do with this query:

SELECT 
  topics.id, 
  topics.topic_title, 
  topics.topic_category, 
  topics.topic_content, 
  topics.author_id, 
  users.name, 
  users.profile_pic, 
  topics.created_at, 
  count(comments.id) AS comments 
FROM 
  topics 
JOIN 
  users 
ON 
  users.id = topics.author_id 
LEFT JOIN 
  comments 
ON 
  topics.id = comments.topic_id 
GROUP BY 
  topics.id 
ORDER BY 
  topics.created_at 
DESC

Which returns an sql result of:

topic_title | created_at | id | topic_category | author_id | topic_content | name | profile_pic | comments

This works fine, the problem is I don't just need an overall comment count. The mood field in the comments table can have 3 possible values (0, 1, 2) and I need to count the amount of comments with each value.

I tried changing

count(comments.id)

in the above query to

count(comments.mood=0) AS happy, count(comments.mood=1) AS sad, count(comments.mood=2) AS angry

but that returns the same value for each of the result fields. Is there any way to do this in a single mySQL query?

Upvotes: 2

Views: 64

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to use sum() to do such operation as

sum(comments.mood=0) as happy,
sum(comments.mood=1) as sad,
sum(comments.mood=2) as angry,

Edit by @Pavel: I'll just share the final query that I used to get the correct results which I based on answers given by @Abhik Chakraborty and @Tomalak.

SELECT 
    topics.id, 
    topics.topic_title, 
    topics.topic_category, 
    topics.topic_content, 
    topics.author_id, 
    users.name AS author_name, 
    users.profile_pic, 
    topics.created_at, 
    IFNULL(SUM(comments.mood=0),0) AS comments_happy, 
    IFNULL(SUM(comments.mood=1),0) AS comments_sad, 
    IFNULL(SUM(comments.mood=2),0) AS comments_angry 
  FROM 
    topics 
  JOIN 
    users 
  ON 
    users.id = topics.author_id 
  LEFT JOIN 
    comments 
  ON topics.id = comments.topic_id 
  GROUP BY 
    topics.id 
  ORDER BY 
    topics.created_at 
  DESC

Upvotes: 3

Related Questions