I want to produce a table with two columns in the form of (country, total_revenue)

This is how the relational model looks like, Each entry in the table orderdetails can produce revenue where its in the form of = quantityordered(a column)* priceEach(also a column).

The revenue an order produces is the sum of the revenue from the orderdetails in the order, but only if the order's status is shipped. The two tables orderdetails and order are related by the column ordernumber.

An order has a customer number that references customer table and the customer table has country field. The total_country_revenue is the sum over all shipped orders for customers in a country.

so far I have tried first producing a table by using group by(using ordernumber or customer number?) to produce a table with columns orderdetails revenue and the customer number to join with customer and use group by again but I keep getting weird results.....

-orderdetails table-

-orders table-

-customers table-

-Result table-

CodePudding user response：

Your description is a bit weird. You are writing that you want to build the sum per country, but in your table which should show the desired outcome, you didn't build a sum and you also don't show the country. Furthermore, you wrote you want to exclude orders that don't have the status "shipped", but your sample outcome includes them. This query will produce the outcome you have described in words, not that one you have added as a table:

SELECT c.country, 
SUM(d.quantityordered * d.price_each) AS total_revenue
FROM
orders o 
JOIN orderdetails d ON o.ordernumber = d.ordernumber
JOIN customers c ON o.customer_num = c.custom_num
WHERE o.status = 'shipped'
GROUP BY c.country;

As you can see, you will need to JOIN your tables and apply a GROUP BY country clause.

A note: You could remove the WHERE clause and add its condition to a JOIN. It's possible this will reduce the execution time of your query, but it might be less readable.

A further note: You could also consider to use a window function for that using PARTITION BY c.country. Since you didn't tag your DB type, the exact syntax for that option is unclear.

A last note: Your sample data looks really strange. Is it really intended an order should be counted as for France and for the USA the same time?

If the query above isn't what you were looking for, please review your description and fix it.