CODEWITHSUNDEEP
mysqlsqljoincountnull
Kane Charles
Kane Charles

Reputation: 389

MySQL Count Where Two Columns are Same and Not Null, Same Table

I have a table with the following data:

mysql> describe Post;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| post_date   | datetime     | NO   |     | NULL    |                |
| in_reply_to | int(11)      | YES  |     | NULL    |                |
| text        | varchar(160) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> select id as "Row ID", user_id as "User ID", post_date as "Post Date", IF(in_reply_to is NULL, "None", in_reply_to) as "In Reply To Post ID:", CONCAT(LEFT(text,40),"...") as "Post Text" from Post;
+--------+---------+---------------------+----------------------+---------------------------------------------+
| Row ID | User ID | Post Date           | In Reply To Post ID: | Post Text                                   |
+--------+---------+---------------------+----------------------+---------------------------------------------+
|      1 |       1 | 2015-08-14 20:38:00 | None                 | This is the original test post that I pu... |
|      2 |       2 | 2015-08-14 20:39:00 | None                 | This is the second post that I put into ... |
|      3 |       5 | 2015-08-14 22:00:00 | 1                    | Hahaha, that post was hilarious. I canno... |
|      4 |       4 | 2015-08-14 23:00:00 | 1                    | Today I saw a cat jump off the roof, ont... |
|      5 |       4 | 2015-08-14 23:00:00 | None                 | Today I saw a cat jump off the roof, ont... |
|     27 |       1 | 2015-09-08 05:53:40 | 2                    | This is a mad reply ay...                   |
|     28 |       1 | 2015-09-08 11:24:05 | None                 | Yolo Swag...                                |
+--------+---------+---------------------+----------------------+---------------------------------------------+
7 rows in set (0.05 sec)

Each of the columns have a description if you are not sure of what they represent. The two columns I am concerned with for this question are id and in_reply_to.

in_reply_to is a NULLABLE FK integer that references id in the same table; if in_reply_to is NULL, it means the post is an original post, if it is an integer value, it is a reply post and represents the id of the post that it is a reply to.

In the below example there are 4 original posts (1, 2, 5, 28) and 3 replies (3, 4, 27), ie 3 is a reply to 1, 4 is also a reply to 1, and 27 is a reply to 2. I'm looking to perform an SQL query that produces output like below:

intended output

Where Num Replies is a COUNT of how rows there are in the same table that have their in_reply_to equal to id; displaying 0 if there are no replies to that post (ie no rows contain the id for the particular post as their in_reply_to column.

Thanks.

Solution (as per Anders' answer):

mysql> SELECT   Post.id, Post.user_id, Post.post_date, Post.in_reply_to, CONCAT(LEFT(Post.text,40)),   IF(counts.count IS NULL, 0, counts.count) AS 'Num of Replies' FROM Post LEFT JOIN    (SELECT      in_reply_to AS id,      COUNT(*) AS count    FROM Post    WHERE in_reply_to IS NOT NULL    GROUP BY in_reply_to) AS counts  ON Post.id = counts.id;
+----+---------+---------------------+-------------+------------------------------------------+----------------+
| id | user_id | post_date           | in_reply_to | CONCAT(LEFT(Post.text,40))               | Num of Replies |
+----+---------+---------------------+-------------+------------------------------------------+----------------+
|  1 |       1 | 2015-08-14 20:38:00 |        NULL | This is the original test post that I pu |              2 |
|  2 |       2 | 2015-08-14 20:39:00 |        NULL | This is the second post that I put into  |              1 |
|  3 |       5 | 2015-08-14 22:00:00 |           1 | Hahaha, that post was hilarious. I canno |              0 |
|  4 |       4 | 2015-08-14 23:00:00 |           1 | Today I saw a cat jump off the roof, ont |              0 |
|  5 |       4 | 2015-08-14 23:00:00 |        NULL | Today I saw a cat jump off the roof, ont |              0 |
| 27 |       1 | 2015-09-08 05:53:40 |           2 | This is a mad reply ay                   |              0 |
| 28 |       1 | 2015-09-08 11:24:05 |        NULL | Random Text                              |              0 |
+----+---------+---------------------+-------------+------------------------------------------+----------------+
7 rows in set (0.00 sec)

Upvotes: 1

Views: 246

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

You can do a join in the traditional way, or you can do the join straight in the new column.

Example:

select a.id, 
(select count(*) from (select 1 as id union all select 1 union all select 2)b where b.id=a.id) as count_of_replies

 from 
    (select 1 as id union all select 1  union all select 2)a

note the 2 subquery 'tables' are both the same table.

Upvotes: 0

Anders
Anders

Reputation: 8577

You need to join two queries on the same table. The first one just selects all the posts, the second one counts the number of replies for each post. It's a left join since you want to include the posts without any replies (that will not be returned from the second query). The IF is there to convert NULL values to 0 for those.

SELECT
  post.id,
  -- Other fields...,
  IF(counts.count IS NULL, 0, counts.count) AS count
FROM post
LEFT JOIN 
  (SELECT
     in_reply_to AS id,
     COUNT(*) AS count
   FROM post
   WHERE in_reply_to IS NOT NULL
   GROUP BY in_reply_to) AS counts
 ON post.id = counts.id

Disclaimar: I have not tested this.

Upvotes: 1

Related Questions