Jump to content

COUNT inside UNION ALL leads to skipping expected results


butlimous

Recommended Posts

MYSQL version: 5.5.27-cll - MySQL Community Server (GPL)

 

Hey :)

 

I have a simple articles and comments table. I want to display the articles along with their comments. I want to union with one select with the comments and another select without the comments on the same table. I have article number 1 with 1 comment and article number 2 with no comments and article number 3 with 2. I use the following query to get the results.

 

SELECT articles.id AS article_id,
comments.id AS comment_id,
comment
FROM articles
LEFT JOIN comments ON comments.aid = articles.id
UNION ALL
SELECT articles.id AS article_id,
NULL,
NULL
FROM articles
GROUP BY article_id
ORDER BY article_id DESC

 

The result I get which is correct:

 

 

article_id | comment_id | comment
3 | 3 | good
3 | 2 | very good
3 | NULL | NULL
2 | NULL | NULL
2 | NULL | NULL
1 | NULL | NULL
1 | 1 | | bad one

 

 

Now if I want to count the comments also I add COUNT to the query and it becomes:

 

 

SELECT articles.id AS article_id,
comments.id AS comment_id,
comment ,
COUNT(DISTINCT comments.id) AS count_comments
FROM articles
LEFT JOIN comments ON comments.aid = articles.id
UNION ALL
SELECT articles.id AS article_id,
NULL,
NULL ,
NULL
FROM articles
GROUP BY article_id
ORDER BY article_id DESC

 

Now the results change after adding the the count column and not all rows are outputted:

 

 

article_id | comment_id | comment
3 | NULL | NULL
2 | NULL | NULL
1 | NULL | NULL
1 | 1 | bad one

 

 

Now the comments aren't displayed except the comment of article 1, ID(2) should be displayed twice for the 2 select commands, and ID(3) should be displayed 3 times (1 for the second select command and 2 for the first select commands as there are 2 comments)

 

I don't know why adding count leads to ths.

 

Thanks

Edited by butlimous
Link to comment
Share on other sites

If you have an aggregation function (COUNT, SUM, etc) it will assume you are aggregating the whole table and return a single row, unless you provide a GROUP BY field/s clause. If you GROUP BY id you will then get a single row for each id with its comment count (and a single comment, probably the first for that id). To get mutiple comments with a GROUP BY id you could use GROUP_CONCAT();

Link to comment
Share on other sites

SELECT articles.id AS article_id,
comments.id AS comment_id,
comment ,
COUNT(DISTINCT comments.id) AS count_comments
FROM articles
LEFT JOIN comments ON comments.aid = articles.id

 

That is the query that your COUNT() is applied against.  That query has no GROUP BY clause to define how the groups need to be created.

 

Basically you applied your GROUP BY to the wrong query.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.