JOIN with aggregation combines related records and then uses functions such as SUM, COUNT, AVG, MIN, or MAX to produce summarized results.
Most business reports require summarized information rather than individual transaction rows.
Calculate total sales for each customer.
SELECT
c.CustomerName,
COUNT(o.OrderID) AS OrderCount,
SUM(o.OrderAmount) AS TotalSales,
AVG(o.OrderAmount) AS AverageOrder
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerName;Used in sales reports, customer dashboards, financial analysis, and operational KPIs.
- JOIN combines related data.
- Aggregate functions summarize rows.
- GROUP BY defines the reporting level.
- LEFT JOIN can retain entities with no transactions.
- Choose COUNT carefully when NULLs are possible.
Why combine JOIN and GROUP BY?
JOIN brings related information together while GROUP BY summarizes that information at the required business level.
Why use LEFT JOIN for customer sales?
It keeps customers who have no orders so they can still appear in the report.