Aggregate functions such as SUM, COUNT, AVG, MIN, and MAX can be used on data produced by a JOIN.
Business reporting frequently requires calculations across related tables rather than data from a single table.
Calculate the total sales amount for each customer.
SELECT
c.CustomerName,
SUM(o.OrderAmount) AS TotalSales,
COUNT(o.OrderID) AS OrderCount
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;This technique is widely used for sales summaries, customer value analysis, transaction reporting, and financial analytics.
- Aggregate functions can be used after JOIN.
- SUM calculates totals.
- COUNT calculates record counts.
- AVG calculates averages.
- GROUP BY is commonly required for grouped summaries.
Can aggregate functions be used with JOIN?
Yes. Aggregate functions can calculate summaries across data returned from joined tables.
Why is GROUP BY often used with JOIN and aggregates?
GROUP BY allows the aggregate calculation to be performed separately for each required business group.