Aggregate functions summarize multiple rows into calculated results. When combined with JOIN, they can summarize related information across tables.
Business reporting frequently requires calculations across related tables.
Calculate total sales and number of orders for every customer.
SELECT
c.CustomerName,
COUNT(o.OrderID) AS TotalOrders,
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 extensively in sales dashboards, customer analytics, financial reports, and operational reporting.
- Aggregate functions summarize joined data.
- COUNT can count related records.
- SUM calculates totals.
- AVG calculates averages.
- MIN and MAX identify minimum and maximum values.
- GROUP BY usually defines the reporting level.
Why combine JOIN with aggregate functions?
To calculate summaries such as totals, counts, and averages across related records.
Why might LEFT JOIN be useful with aggregates?
LEFT JOIN preserves entities with no related records, allowing them to appear in the report.