CODEWITHSUNDEEP
sqloracle-database
loomie
loomie

Reputation: 616

oracle sql query to count based on another query

I want to know which customer from which country did the most orders. So I have a sales_table with the customer_ids. But the country_id is in the customer_table. So I need somehow to count the customers based on the country count... But I have no idea how to do it. I

I know how to count the customers.

select count(cust_id)
from sh_sales

and how to count the countries

select count(country_id)
from sh_customers

but i want to count the country based on the customer_id which is most often used in the sh_sales table

so it should somehow be

select count(country_id)
from sh_customers
where sh_sales.customer ????

I really need some help here :)

Upvotes: 0

Views: 145

Answers (1)

Matt Busche
Matt Busche

Reputation: 14333

This will count the records in the sh_sales table and group out by each country_id from the customers table

SELECT country_id, count(s.cust_ID)
FROM sh_customers c
    INNER JOIN sh_sales s ON c.cust_id = s.cust_id
GROUP BY country_id

If, for some reason, you could have a customer record, but no sales then you can use LEFT OUTER JOIN to return a NULL for countries without any sales

Upvotes: 3

Related Questions