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?
The most common way of joining three tables goes something like this: SELECT * FROM Table1 INNER JOIN Table2 ON Condition INNER JOIN Table3 ON Condition; This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).
The syntax for multiple joins: SELECT column_name1,column_name2,.. FROM table_name1 INNER JOIN table_name2 ON condition_1 INNER JOIN table_name3 ON condition_2 INNER JOIN table_name4 ON condition_3 . . . Note: While selecting only particular columns use table_name.
Conclusion. In this short tutorial, you have seen how the COUNT/GROUP BY/JOIN combination can be used in SQL to aggregate entries across multiple tables. While a GROUP BY query can accomplish this simply enough when working with just one table, the situation can become more complex when working across multiple tables.
To achieve this for multiple tables, use the UNION ALL. select sum(variableName. aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName; Let us implement the above syntax.
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.
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.
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 :)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With