Aggregate Functions

A normal SELECT displays individual rows, but real questions often ask “how many,” “what total,” “what average,” or “what are the lowest and highest values?” Aggregate functions read many rows and produce a summary. The five most common are COUNT, SUM, AVG, MIN, and MAX.

COUNT: count rows or known values

COUNT(*) counts every matching row. COUNT(phone) counts only rows where phone is not NULL. Total users and users with known phone numbers can therefore differ. COUNT also works with WHERE, such as counting Tokyo users.

SELECT COUNT(*) AS all_rows,
       COUNT(phone) AS users_with_phone
FROM users
WHERE city = 'Tokyo';

SUM: add numeric values

SUM(quantity) answers how many items all orders contain. It is not the number of orders: if three rows have quantities 1, 2, and 4, COUNT is 3 while SUM is 7.

AVG: calculate a mean

AVG(price) adds all non-NULL prices and divides by the number of prices that participated. Give the result a clear alias such as average_price.

MIN and MAX: find both ends

MIN(price) returns the lowest price and MAX(price) the highest. They also work with dates to find the earliest and latest order dates.

The five aggregates each have a clear role: COUNT counts, SUM totals, AVG averages, MIN finds the smallest value, and MAX the largest.

Calculate several statistics together

Several aggregates can share one SELECT to produce a compact overview. Aggregates normally ignore NULL; COUNT(*) is the exception because it counts rows. For prices 40, NULL, and 80: COUNT(*) is 3, COUNT(price) is 2, SUM is 120, and AVG is 60.

Do not mix in a normal column without a plan

SELECT name, AVG(price) FROM products has a logical problem: AVG collapses many rows to one result while name belongs to individual rows. The database cannot know which product name belongs beside the overall average. For separate statistics by category, use GROUP BY in the next section.

SELECT COUNT(price) AS priced_products,
       SUM(price) AS total_price,
       AVG(price) AS average_price,
       MIN(price) AS lowest_price,
       MAX(price) AS highest_price
FROM products;