Aggregate functions: COUNT, SUM, AVG and their sharp edges

An aggregate function takes a whole column of values and boils it down to one number: a count, a total, an average, a smallest or largest. You met them briefly with GROUP BY; here's what actually trips people up.

🎯 Explain Like I'm Hired Aggregates answer "one number" questions about a pile of rows: how many, what's the total, what's the average. The catch most beginners miss is how they treat blanks: most of them quietly skip blank values instead of counting them as zero. Example: asked "what's our average order value?", AVG(amount) ignores orders where amount is blank, so if you want blanks counted as ₹0, you have to say so with AVG(COALESCE(amount, 0)). Knowing that distinction is exactly the kind of thing a SQL screen checks.

COUNT(*) vs COUNT(column): not the same thing

SELECT
  COUNT(*)          AS all_rows,       -- counts every row
  COUNT(phone)      AS has_phone,      -- counts rows where phone is NOT blank
  COUNT(DISTINCT country) AS countries -- counts unique non-blank countries
FROM customers;

• COUNT(*) counts every row, blanks and all.
• COUNT(phone) counts only rows where phone is not blank, a handy way to ask "how many customers gave us a phone number?"
• COUNT(DISTINCT country) counts unique values.

SUM, AVG, MIN, MAX all skip blanks

Just like COUNT(column), the others ignore blank values rather than treating them as zero. AVG of 10, (blank), 20 is 15. If you need blanks treated as zero, wrap them: AVG(COALESCE(x, 0)).

The interview trap: counting with a condition

You often want "how many orders were delivered?" alongside other totals, in one query. The trick is COUNT with a CASE:

SELECT
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered
FROM orders;

The CASE returns 1 for delivered rows and blank otherwise, and since COUNT(column) skips blanks, you get exactly the delivered count. This "conditional count" shows up constantly in real reporting.

---

Next: String & date functions →