We know how to calculate the total price for a single order. However, real-world reports are typically more complicated. For example, you often need to provide the total values for multiple orders. The query below shows you how to do this:
c.company_name AS customer_company_name,
SUM(unit_price * quantity) AS total_price
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
JOIN order_items oi
ON o.order_id = oi.order_id
WHERE o.ship_country = 'France'
GROUP BY o.order_id, c.company_name;
We want to find the total price (before discount) for each order shipped to France. We also want the order ID and the name of the company that placed the order. That's why we used a
GROUP BY clause.
Even though it would be enough to group the rows by
order_id, we also added
company_name in the
GROUP BY. This is because every column in the
SELECT that isn't used with an aggregate function (e.g.,
SUM(), etc.) must appear in the
GROUP BY clause. Otherwise, the query won't work.