COUNT inside UNION ALL leads to skipping expected results

[butlimous]

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 February 6, 2013 by butlimous

[Barand]

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();

[butlimous]

Hey Barand

 

So should GROUP_CONCAT be used instead of group by at the bottom or COUNT should be put inside the groupconcat or what??

 

Thanks

[butlimous]

Tried replacing

COUNT(DISTINCT comments.id)

with

GROUP_CONCAT(comments.id)

but it's still producing the same results as the count distinct

[kicken]

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.

 

[butlimous]

Yup just discovered that kicken...thanks