CODEWITHSUNDEEP
sql
SunAwtCanvas
SunAwtCanvas

Reputation: 1460

SQL - Find total number of orders made for each customer

I have 2 tables customer and orders. Each customer can place multiple orders

I want the results to show like this:

enter image description here

Here is my current code which only returns all the distinct customer num

select distinct c.customer_num  
from customer c 
inner join orders o on o.CUSTOMER_NUM = c.customer_num

Upvotes: 1

Views: 5010

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658907

Assuming you are only interested in customers who actually ordered something, to get your desired result, you don't even need to involve table customer. Just:

SELECT customer_num, count(*) AS orders_made
FROM   orders
GROUP  BY customer_num
ORDER  BY customer_num;  -- seems like you want this order?

Upvotes: 2

Fahmi
Fahmi

Reputation: 37483

Try the below - using count() aggregation with group by

select c.customer_num,count(order_num) 
from customer c inner join orders o on  o.CUSTOMER_NUM = c.customer_num
group by c.customer_num

Upvotes: 2

Related Questions