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.CompanyName AS CustomerCompanyName,
SUM(UnitPrice * Quantity) AS TotalPrice
FROM Orders O
JOIN Customers C
ON O.CustomerID = C.CustomerID
JOIN OrderItems OI
ON O.OrderID = OI.OrderID
WHERE O.ShipCountry = N'France'
GROUP BY O.OrderID, C.CompanyName;
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
OrderID, we also added
CompanyName 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.