Taking count from multiple tables using left join

Question

I am using the following query to get the count from multiple tables:-

SELECT 
  b.sales_title,
  c.cat_name,
  count(b.sales_id) as cnt,
  count(e.comment_id) as coun  
FROM tb_sale_report a 
  inner join tbl_sales    b on a.sales_id=b.sales_id 
  inner join  tb_category c on c.cat_id=b.category_id 
  left  join tb_comment   e on b.sales_id=e.sales_id
GROUP BY b.sales_title

I am trying to achieve

sales_title |   cat_name    |   cnt  |  coun
--------------------------------------------------
Affiliate   |   Kids toys   |   8    |  0
Date Check  |   Handbags    |   26   |  1
Date Date   |   My Category |   4    |  1
Future Date |   Handbags    |   3    |  0
Giovanni    |   Kids toys   |   4    |  1

But instead of that i am getting the wrong count, the values for the column coun is getting wrong like this,

sales_title  |  cat_name    |   cnt  |  coun
---------------------------------------------
Affiliate    |  Kids toys   |   8    |  0
Date Check   |  Handbags    |   26   |  26
Date Date    |  My Category |   4    |  4
Future Date  |  Handbags    |   3    |  0
Giovanni     |  Kids toys   |   4    |  4

How can i write the query to achieve my goal.Need help, i am a newbie to programming.Thanks in advance

mathematical.coffee · Accepted Answer · 2012-02-10 05:57:15Z

1

It's hard to tell unless you tell us your table structure/give a small snippet of data that replicates your problem. It looks like your comments table is being replicated in order to perform the join to the tbl_sales table, and so there are duplicates in the count.

Try COUNT(DISTINCT e.comment_id) as coun to eliminate this problem.

answered Feb 10, 2012 at 5:57

mathematical.coffee

57.1k15 gold badges160 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Taking count from multiple tables using left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related