CODEWITHSUNDEEP
mysqljoingroup-bycountunion-all
Vishwas Bhatnagar
Vishwas Bhatnagar

Reputation: 168

Join two count query is not giving right result

I trying to join two query to compare count

SELECT count(customer_id) , customer_id
FROM `blog_post`
group by customer_id

and second query is

SELECT count(customer_id)
FROM `blog_comment`
WHERE `is_admin` IS NOT NULL
group by customer_id

Joined query i created is

SELECT count(post.customer_id) as post_count , post.customer_id ,
       count(comment.customer_id) 
FROM `blog_post` as post 
    left join blog_comment as comment on post.customer_id = comment.customer_id 
WHERE `is_admin` IS NOT NULL 
GROUP BY post.customer_id

I am not getting the same result as running them individually , what am i doing wrong

Upvotes: 1

Views: 32

Answers (1)

forpas
forpas

Reputation: 164069

For your requirement you need a FULL OUTER JOIN of the 2 queries, which is not supported by MySql and can only be simulated with LEFT/RIGHT joins and UNION ALL.

Another way to do what you want is to use UNION ALL for the 2 queries and aggregate on the results:

SELECT customer_id, 
       MAX(post_count) post_count,
       MAX(comment_count) comment_count 
FROM (
  SELECT customer_id, COUNT(*) post_count, 0 comment_count
  FROM `blog_post`
  GROUP BY customer_id
  UNION ALL
  SELECT customer_id, 0, COUNT(*)
  FROM `blog_comment`
  WHERE `is_admin` IS NOT NULL
  GROUP BY customer_id
) t
GROUP BY customer_id

Upvotes: 1

Related Questions