Inner join with count() on three tables

Question

Simple and fast question, i have those tables:

//table people
| pe_id | pe_name |
| 1  | Foo  |
| 2  | Bar  |
//orders table
| ord_id | pe_id | ord_title   |
|   1    |   1   | First order |
|   2    |   2   | Order two   |
|   3    |   2   | Third order |
//items table
| item_id | ord_id | pe_id | title  |
|   1     |   1    |   1   | Apple  |
|   2     |   1    |   1   | Pear   |
|   3     |   2    |   2   | Apple  |
|   4     |   3    |   2   | Orange |
|   5     |   3    |   2   | Coke   |
|   6     |   3    |   2   | Cake   |

I need to have a query listing all the people, counting the number of orders and the total number of items, like that:

| pe_name | num_orders | num_items |
| Foo  |    1       |   2       |
| Bar  |    2       |   4       |

But i can not make it work! I tried

SELECT
    people.pe_name,
    COUNT(orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
    INNER JOIN items ON items.pe_id = people.pe_id
GROUP BY
    people.pe_id;

But this returns the num_* values incorrect:

| name | num_orders | num_items |
| Foo  |    2       |   2       |
| Bar  |    8       |   8       |

I noticed that if i try to join one table at time, it works:

SELECT
    people.pe_name,
    COUNT(orders.ord_id) AS num_orders
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
GROUP BY
    people.pe_id;

//give me:
| pe_name | num_orders |
| Foo     |          1 |
| Bar     |          2 |

//and:
SELECT
    people.pe_name,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN items ON (items.pe_id = people.pe_id)
GROUP BY
    people.pe_id;
//output:
| pe_name | num_items |
| Foo     |         2 |
| Bar     |         4 |

How to combine those two queries in one?

Answer 1

Your solution is nearly correct. You could add DISTINCT:

SELECT
    people.pe_name,
    COUNT(distinct orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
    INNER JOIN items ON items.pe_id = people.pe_id
GROUP BY
    people.pe_id;

Answer 2

It's make more sense to join the item with the orders than to the people !

SELECT
    people.pe_name,
    COUNT(distinct orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON orders.pe_id = people.pe_id
         INNER JOIN items ON items.ord_id = orders.ord_id
GROUP BY
    people.pe_id;

Join the items with the people provoke a lot of doublons. For example, the cake items in order 3 will be link with the order 2 via the join between the people, and you doesn't want this to happen !!

So :

1- You need a good understanding of your schema. Items are link to orders, and not to people.

2- You need to count distinct orders for one people, else you will count as many items as orders.

Answer 3

As Frank pointed out, you need to use DISTINCT. Also, since you are using composite primary keys (which is perfectly fine, BTW) you need to make sure that you use the whole key in your joins:

SELECT
    P.pe_name,
    COUNT(DISTINCT O.ord_id) AS num_orders,
    COUNT(I.item_id) AS num_items
FROM
    People P
INNER JOIN Orders O ON
    O.pe_id = P.pe_id
INNER JOIN Items I ON
    I.ord_id = O.ord_id AND
    I.pe_id = O.pe_id
GROUP BY
    P.pe_name

Without I.ord_id = O.ord_id it was joining each item row to every order row for a person.