CODEWITHSUNDEEP
mysqlsql
user8897159
user8897159

Reputation:

How do I list the count of orders in each nation, along with the nation name?

I have a table for orders, a table for nations, and a table for customers. I am trying to get the # of orders by country. I tried 

SELECT
    COUNT(*)
FROM 
    (
        nation
        INNER JOIN customer ON nation.n_nationkey = customer.c_nationkey
    )
    INNER JOIN orders ON customer.c_custkey = orders.O_CUSTKEY

but it doesn't give me what I want. How do I get the amount of nations with the number of orders from each one?

Upvotes: 2

Views: 71

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35593

You need a group by and at least one column listed in that clause e.g. 

SELECT
      nation.n_nationkey, COUNT(*) order_count
FROM nation
INNER JOIN customer ON nation.n_nationkey = customer.c_nationkey
INNER JOIN orders ON customer.c_custkey = orders.O_CUSTKEY
GROUP BY
     nation.n_nationkey

This will cause he query to produce one row for each unique value of n_nationkey and will count all rows related to the n_nationkey value of each row. Add or substitute the nation name column in the selct and group by clauses.

Upvotes: 2

Related Questions