Can I select 2 tables data in one table with a sum values?

Question

I have two tables linked with a foreign key, Customer and Orders table

Table Customers

Cust_ID	Cust_name
1	John
2	Tom

Orders

order_id	Cust_ID	Orders
1	1	Java Book
2	1	Payton Book
3	2	MySQL Book

So how many Book 'John' Ordered?

I wanna to show like this, please anyone help me: show me the query.

name	order
John	2
Tomi	1

Answer 1

This should work

select name, count(*) as order from Customers join Orders on Customers.Cust_ID = Orders.Cust_ID group by Customers.CustID;

Answer 2

You can query as below:

SELECT   Cust_name, count(o.order_id) as Orders
FROM     Customers c 
JOIN     Orders o 
  ON       
           c.Cust_ID = o.Cust_ID 

GROUP BY   c.CustID;

Answer 3

You can do it easily with join and group by clause or using subquery. Since you want to use order as column alias and it's a reserve word you need to wrap it with double quotes("order").

DB-Fiddle:

Schema and insert statements:

 create table Customers(Cust_ID int, Cust_name varchar(50));
 insert into customers values(1,    'John');
 insert into customers values(2,    'Tom');
 
 
 create table Orders(order_id int,  Cust_ID  int, Orders varchar(50));
 insert into Orders values(1,   1,  'Java Book');
 insert into Orders values(2,   1,  'Payton Book');
 insert into Orders values(3,   2,  'MySQL Book');
 

Query#1 (using join and group by clause)

 SELECT   Cust_name name, count(o.order_id) as "order"
 FROM     Customers c 
 JOIN     Orders o 
   ON       
            c.Cust_ID = o.Cust_ID 
 GROUP BY   c.Cust_name;

Output:

name	order
John	2
Tom	1

Query:(using subquery)

 SELECT   Cust_name name, (select count(order_id) from Orders o where o.Cust_ID=c.Cust_ID) as "order"
 FROM     Customers c 

Output:

name	order
John	2
Tom	1

db<>fiddle here