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?
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 :)
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;
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
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.
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.
Source: Stackoverflow.com