skip to Main Content

I’ve been required while online self learning to do as title shows.
this is the info of the quesion:
Accounting
Instruction
Write a query to select the amount of sales by country. Order the result by descending amount.
Produce exactly two columns with the country code and the sales amount, as shown in the example below. Column aliases are not important.
Example

| country || total |
--------------------
FR           318421.25
IT           74757.74
ES           65175.77

This is what I tried (tried multiple ways)

SELECT users.country as 'Country', SUM(products.price * order_items.quantity) as 'Total Sales'
FROM users
INNER JOIN order_items ON users.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id
ORDER BY users.country DESC;

And I get this result, but im supposed to get the other countries as well, not just DE.

Country || Total Sales
DE          363282.61

Any help appreciated, thank you.

2

Answers


  1. Chosen as BEST ANSWER

    Here's the working solution thanks to akina:

    SELECT users.country as 'Country', SUM(products.price * order_items.quantity) as Total_Sales 
    FROM users INNER JOIN order_items ON users.id = order_items.order_id 
    INNER JOIN products ON order_items.product_id = products.id
    GROUP BY users.country
    ORDER BY Total_Sales DESC;
    

    Thanks guys


  2. SELECT u.country Country,
           SUM(p.price * oi.quantity) Total_Sales
    FROM users u,
         order_items oi,
         products p
    WHERE u.id = oi.id
      AND oi.product_id = p.id
    ORDER BY Total_Sales ASC;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search