3

I have 3 tables

products table productid productname --------- ----------- 1 product 1 2 product 2 5 product 3 10 product 4 11 product 5 12 product 6 accounts_products table id productid accountid -- --------- --------- 1 1 accountid 1 2 10 accountid 2 3 2 accountid3 leads_products table id productid leadid -- --------- --------- 1 1 leadid 1 2 5 leadid 2 3 2 leadid 3

I am trying to count how many total products are in leads_products and accounts_products tables based on the same productid's.

Expected result Product ID Product Name Total ----------- ------------ -------- 1 product 1 2 2 product 2 2 5 product 3 1 10 product 4 1

I tried so far

SELECT p.productid as 'Product ID', p.productname as 'Product Name', COUNT(*) as 'Total' FROM products p INNER JOIN leads_products l ON (l.productid=p.productid) INNER JOIN accounts_products a ON (a.productid=p.productid) GROUP BY p.productname,p.productid

Above query counts and display higher number than expected.

I hope it makes sense.

Improve this question
2

2 Answers 2

Reset to default
10

Try this:

SELECT p.productid as 'Product ID', p.productname as 'Product Name', (SELECT COUNT(*) FROM leads_products AS l WHERE l.productid = p.productid) + (SELECT COUNT(*) FROM accounts_products AS a WHERE a.productid=p.productid) AS 'Total' FROM products AS p

http://www.sqlfiddle.com/#!2/f8472/5

Alternative approach using JOIN (better performance):

SELECT p.productid as 'Product ID', p.productname as 'Product Name', IFNULL(l.count, 0) + IFNULL(a.count, 0) as 'Total' FROM products AS p LEFT JOIN ( SELECT productid, COUNT(*) AS count FROM leads_products GROUP BY productid ) AS l ON l.productid = p.productid LEFT JOIN ( SELECT productid, COUNT(*) AS count FROM accounts_products GROUP BY productid ) AS a ON a.productid = p.productid

http://www.sqlfiddle.com/#!2/f8472/33

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

6 Comments

Can you provide an explanation of why the OP's query did not perform as expected?
He's calculating the cross product, not the sum. Instead of 1+1 he gets 1*1, and instead of 1+0 he gets 1*0.
@Mark your query almost works. It displays every record in products table. If there is no count it displays counts as 0. Any ideas? Thank you for your fast reply.
@librium: Hmm... tricky. If only there were some way to remove rows from a result set... shame that's not possible in SQL...
@librium: I was joking. :) Use WHERE or HAVING.
|
-1

I did it with a left join of p.products to preserve all the products then counted the products in each list and added them together. Worked first try. There's probably a billion ways to do this.

SELECT p.productid 'Product ID', p.productname 'Product Name', COUNT(a.accountid) + COUNT(l.leadid) total FROM products p LEFT JOIN accounts_products a ON p.productid = a.productid LEFT JOIN leads_products l ON p.productid = l.productid GROUP BY p.productid

http://www.sqlfiddle.com/#!2/f8472/37

Improve this answer

6 Comments

Add one extra row and it gives the wrong result: sqlfiddle.com/#!2/8ca24/1
@MarkByers - Did you check it yourself? I got OP's desired result
@Scarecrow736: He even linked you to a sqlfiddle demonstrating his point. -1
@eggyal - I made my post before there were any answers no need to be an ass
@Scarecrow736: I was referring to your comment to MarkByers.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.