1

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

forpas
  • 160,666
  • 10
  • 38
  • 76
Vishwas Bhatnagar
  • 168
  • 5
  • 21

1 Answers1

1

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
forpas
  • 160,666
  • 10
  • 38
  • 76
  • if possible can you please explain query syntax , t is alias for ? and 0 after post count is for @forpas? – Vishwas Bhatnagar Jan 06 '21 at 14:57
  • 1
    All subqueries/derived tables in MySql must be aliased. t is the alias of the subquery after FROM clause of the main query. – forpas Jan 06 '21 at 14:59