MYSQL count related rows in 2 tables

Question

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.

Test Schema: sqlfiddle.com/#!2/f8472. With OP's sample query: sqlfiddle.com/#!2/f8472/1 — mellamokb
– mellamokb, Commented Apr 27, 2012 at 18:06

Mark Byers · Accepted Answer · 2012-04-27 18:18:31Z

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

edited Apr 27, 2012 at 18:18

answered Apr 27, 2012 at 18:08

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

6 Comments

eggyal Over a year ago

Can you provide an explanation of why the OP's query did not perform as expected?

Mark Byers Over a year ago

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.

librium Over a year ago

@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.

Mark Byers Over a year ago

@librium: Hmm... tricky. If only there were some way to remove rows from a result set... shame that's not possible in SQL...

Mark Byers Over a year ago

@librium: I was joking. :) Use WHERE or HAVING.

|

Scarecrow · Accepted Answer · 2012-04-27 18:19:53Z

-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

answered Apr 27, 2012 at 18:19

Scarecrow

1831 silver badge7 bronze badges

6 Comments

Mark Byers Over a year ago

Add one extra row and it gives the wrong result: sqlfiddle.com/#!2/8ca24/1

Scarecrow Over a year ago

@MarkByers - Did you check it yourself? I got OP's desired result

eggyal Over a year ago

@Scarecrow736: He even linked you to a sqlfiddle demonstrating his point. -1

Scarecrow Over a year ago

@eggyal - I made my post before there were any answers no need to be an ass

eggyal Over a year ago

@Scarecrow736: I was referring to your comment to MarkByers.

|

Collectives™ on Stack Overflow

MYSQL count related rows in 2 tables

2 Answers 2

6 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related