CODEWITHSUNDEEP
mysqlsql
Linesofcode
Linesofcode

Reputation: 5891

MySQL COUNT multiple tables with JOIN

I have a problem trying to select the total from multiple tables using joins. The results of COUNT are not correct.

I have three tables:

Customers
id -> Primary/Autoincrement
name

Documents
id -> Primary/Autoincrement
customer_id

Documents_items
id -> Primary/Autoincrement
document_id

And I would like to obtain the total, grouped by customer name, of documents and documents items.

    SELECT cust.name, 
           COUNT(doc.id), 
           COUNT(item.id)
      FROM customers AS cust
INNER JOIN documents AS doc ON doc.customer_id = cust.id
INNER JOIN documents_items AS item ON item.document_id = doc.id
  GROUP BY cust.name

The problem is that the result of COUNT(doc.id) is equal to the result of COUNT(item.id) which is not correct.

You can see a demo example of the error in SQLFiddle.

Input example:

INSERT INTO customers VALUES('John')
INSERT INTO documents VALUES(1)
INSERT INTO documents_items VALUES(1), VALUES(1)

Output expected:

Name     |    Total Docs    | Total Items
John              1               2

Current output:

Name     |    Total Docs    | Total Items
John              2               2

Upvotes: 3

Views: 59

Answers (2)

Arnold Daniels
Arnold Daniels

Reputation: 16553

You want to count the distinct document id's and item id's.

    SELECT cust.name, 
           COUNT(DISTINCT doc.id), 
           COUNT(DISTINCT item.id)
      FROM customers AS cust
INNER JOIN documents AS doc ON doc.customer_id = cust.id
INNER JOIN documents_items AS item ON item.document_id = doc.id
  GROUP BY cust.name

Upvotes: 6

Raging Bull
Raging Bull

Reputation: 18737

Try this way:

SELECT T1.name,T1.Docs,T2.Items
FROM 
( SELECT cust.name, COUNT(doc.id) as Docs
  FROM customers AS cust
  INNER JOIN documents AS doc ON doc.customer_id = cust.id
  GROUP BY cust.name) T1 JOIN

( SELECT cust.name, COUNT(item.id) as Items
  FROM customers AS cust
  INNER JOIN documents AS doc ON doc.customer_id = cust.id
  INNER JOIN documents_items AS item ON item.document_id = doc.id
  GROUP BY cust.name) ON T1.name =T2.name

Explanation:

You have to generate two result with each count. And then join those result with name field.

First inner query will generate the name and count of docs. Second inner query will generate the name and count of items. Then we will join those queries on the name field.

Upvotes: 0

Related Questions