MySQL Count Sum Avg
COUNT(), SUM(), and AVG() are the three most frequently used aggregate functions for counting rows, totaling numeric data, and computing averages.
The COUNT() Function
COUNT() returns the number of rows that match a given criterion. COUNT(*) counts every row regardless of NULL values, while COUNT(column_name) counts only the rows where that specific column is not NULL, which is a subtle but important distinction.
Count total customers and customers with a fax number
SELECT COUNT(*) AS TotalCustomers,
COUNT(Fax) AS CustomersWithFax
FROM Customers;The SUM() Function
SUM() adds together every numeric value in a column, ignoring any NULL values along the way. It is commonly used to calculate totals, such as total quantity sold or total revenue across a set of orders.
Total quantity of all order line items
SELECT SUM(Quantity) AS TotalUnitsSold
FROM OrderDetails;The AVG() Function
AVG() calculates the arithmetic mean of a numeric column by dividing the sum of the values by the count of non-NULL values. Like SUM() and COUNT(), it automatically excludes NULL values from its calculation.
Average price across all products
SELECT AVG(Price) AS AveragePrice
FROM Products;Combining All Three With GROUP BY
COUNT(), SUM(), and AVG() are frequently used together in the same query to build a complete summary report per group, such as showing how many orders, how much total revenue, and what average order value each customer generated.
Per-customer order count, total quantity, and average quantity
SELECT CustomerID,
COUNT(OrderDetailID) AS LineItems,
SUM(Quantity) AS TotalQuantity,
AVG(Quantity) AS AvgQuantityPerLine
FROM OrderDetails
GROUP BY CustomerID;- COUNT(*) counts all rows; COUNT(column) counts only non-NULL values in that column
- SUM() and AVG() automatically skip NULL values rather than treating them as zero
- Use DISTINCT inside these functions, e.g. COUNT(DISTINCT column), to avoid counting duplicates
- Always alias aggregate results with AS for readable output
