7

I'm trying to COUNT() on multiple tables in one query, but I can't get it to work. Here's what I have so far:

TABLES:

table1
---------------------
id | name
---------------------
 1 | test
 2 | test2


table2
---------------------
id | table1_id
---------------------
 1 | 1
 2 | 1
 3 | 1


table3
---------------------
id | table2_id
---------------------
 1 | 1


table4
---------------------
id | size | table3_id
---------------------
 1 | 1024 | 1
 1 | 200  | 1

SQL:

SELECT
    table1.name,
    COUNT(table2.table1_id) AS table2_count,
    COUNT(table3.table2_id) AS table3_count,
    COUNT(table4.table3_id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM
    table1
LEFT JOIN table2
    ON table1.id = table2.table1_id
LEFT JOIN table3
    ON table2.id = table3.table2_id
LEFT JOIN table4
    ON table3.id = table4.table3_id
WHERE
    table1.id = 1

Results I'm getting from the above query:

name | table2_count | table3_count | table4_count | table4_size
---------------------------------------------------------------
test |      4       |      2       |      2       |    1224

Results that I should be getting:

name | table2_count | table3_count | table4_count | table4_size
---------------------------------------------------------------
test |      3       |      1       |      2       |    1224
Improve this question

2 Answers 2

Reset to default
9

You will need to use DISTINCT, but also you need to count the IDs, not the foreign keys:

SELECT
    table1.name,
    COUNT(DISTINCT table2.id) AS table2_count,
    COUNT(DISTINCT table3.id) AS table3_count,
    COUNT(DISTINCT table4.id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1

Here is a fiddle.

Explanation: The DISTINCT key word eliminates all duplicate values resulting in a list of unique values.

If you run your query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          1          200 
test  1          (null)     (null)     (null) 
test  1          (null)     (null)     (null)

So if you add the COUNT() and SUM(), you obviously get:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224

However, using DISTINCT with your query won't help because you can clearly see the duplicate values, which will result in:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1224

Now, if you run my query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          2          200 
test  2          (null)     (null)     (null) 
test  3          (null)     (null)     (null)

If you add the COUNT() and SUM(), you get exactly the same results like your query:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224

However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT, you get:

name  table1_id  table2_id  table3_id  size
test  3          1          2          1224
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Genius! It worked, can you explain the logic behind it? Thanks!
I've added a full explanation with examples.
This is definitely very helpful. Thanks for taking the time to explain all of this. I'll comeback once I'm reputation 15 to upvote you!
I upvoted your question because it is well structured and you want to understand, not only a copy/paste solution.
0

Can you try this way

SELECT  table1.name,
    (SELECT COUNT(table2.table1_id) WHERE  table1.id = table2.table1_id ) AS table2_count,
    (SELECT COUNT(table3.table2_id) WHERE  table2.id = table3.table1_id ) AS table3_count,
    (SELECT COUNT(table4.table3_id) WHERE  table3.id = table4.table1_id ) AS table4_count,
    (SELECT SUM(table4.size) WHERE  table3.id = table4.table1_id ) AS table4_size
    FROM
    table1
    WHERE
    table1.id = 1
Improve this answer

1 Comment

I'm not saving table1 id on table3 nor table4. So, I'll get a syntax error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.