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

One needs to understand what a JOIN or a series of JOINs does to a set of data. With strae's post, a pe_id of 1 joined with corresponding order and items on pe_id = 1 will give you the following data to "select" from:

[ table people portion ] [ table orders portion ] [ table items portion ]

| people.pe_id | people.pe_name | orders.ord_id | orders.pe_id | orders.ord_title | item.item_id | item.ord_id | item.pe_id | item.title |

| 1 | Foo | 1 | 1 | First order | 1 | 1 | 1 | Apple |
| 1 | Foo | 1 | 1 | First order | 2 | 1 | 1 | Pear |

The joins essentially come up with a cartesian product of all the tables. You basically have that data set to select from and that's why you need a distinct count on orders.ord_id and items.item_id. Otherwise both counts will result in 2 - because you effectively have 2 rows to select from.

Answer 2

select pe_name,count( distinct b.ord_id),count(c.item_id) 
 from people  a, order1 as b ,item as c
 where a.pe_id=b.pe_id and
b.ord_id=c.order_id   group by a.pe_id,pe_name

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.

Answer 4

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 5

It makes more sense to join the item with the orders than with 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;

Joining the items with the people provokes a lot of doublons. For example, the cake items in order 3 will be linked with the order 2 via the join between the people, and you don'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 person, else you will count as many items as orders.

Answer 6

i tried putting distinct on both, count(distinct ord.ord_id) as num_order, count(distinct items.item_id) as num items

its working :)

    SELECT
         people.pe_name,
         COUNT(distinct orders.ord_id) AS num_orders,
         COUNT(distinct 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;

Thanks for the Thread it helps :)