oracle sql query to count based on another query

Question

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 :)

Matt Busche · Accepted Answer · 2013-03-11 15:53:53Z

3

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

answered Mar 11, 2013 at 15:53

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

loomie Over a year ago

So now it displays me every country and right next to it the count of customers from this country, correct?

Collectives™ on Stack Overflow

oracle sql query to count based on another query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related