Counting, Summing, Averaging — The Big Picture

So far we've learned to list, filter, and sort individual rows. But most of the questions managers actually ask aren't about individual rows at all — they're about the whole picture. How many customers do we have? What's our average order value? Which product brought in the most revenue?

These are summary questions, and SQL answers them with a family of tools called aggregate functions: COUNT, SUM, AVG, MIN, and MAX. They take a pile of rows and collapse them into a single number.

The meeting analogy

Think about a weekly team meeting. The manager doesn't ask for a printout of all 2,000 transactions. She asks: "how much did we sell this week?" "how many new customers did we sign?" "what was our biggest single order?"

That's summarizing. And it's exactly what these functions do — but instantly, without a human scrolling through a spreadsheet.

COUNT: how many?

COUNT counts rows. The most common version is COUNT(*), which counts every row in the result.

Let's bring in a new table for Soko Fresh — orders, holding each sale that came through the shop (many paid via M-Pesa):

The question: how many orders have we received?

SELECT COUNT(*) FROM orders;

Plain English: "Count all rows in the orders table."

The answer is a single number: 6. Notice there's no FROM-less query — COUNT(*) still needs a table to count from.

You can also count specific columns. COUNT(amount) counts rows where amount is not empty, while COUNT(*) counts every row. For now, think of COUNT(*) as "how many rows," which is what you want 90% of the time.

SUM: how much?

SUM adds up all the values in a numeric column. The question: what's the total value of all orders?

SELECT SUM(amount) FROM orders;

Plain English: "Add up the amount column across all orders."

The answer: 12,500 + 4,800 + 23,000 + 8,900 + 15,000 + 7,600 = 71,800. That's Soko Fresh's total order value, in one number.

AVG: what's the typical value?

AVG computes the average (mean) of a column. What's the average order value?

SELECT AVG(amount) FROM orders;

Plain English: "What's the average of the amount column?"

71,800 ÷ 6 ≈ 11,967. Knowing the average order is ~12,000 shillings tells you far more than any single order does — it's the "typical" customer spend.

MIN and MAX: the extremes

MIN and MAX find the smallest and largest values. Together:

SELECT MIN(amount), MAX(amount) FROM orders;

Plain English: "What's the smallest amount and the largest amount?"

The answer: 4,800 and 23,000. The cheapest order was 4,800; the biggest was 23,000. You could have found these with ORDER BY and LIMIT from the last post — but MIN/MAX give you both in one line, without listing any rows.

GROUP BY: summarizing in groups

Here's where things get powerful. So far every summary collapsed the entire table into one number. But usually you want a number per group — say, sales per day, or order count per customer.

GROUP BY does exactly that: it splits the rows into groups, then runs your aggregate once per group.

How many orders has each customer placed?

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

Plain English: "Show me the customer id and the count of rows, grouped by customer id."

The result:

Customer 1 placed two orders, customer 2 placed two, and customers 3 and 4 placed one each. The key rule: whatever column you put in GROUP BY, you list in SELECT alongside your aggregate. If it's not aggregated, it must be grouped.

Total revenue per customer

Let's make it more useful. Instead of just counting orders, show the total value each customer has ordered:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Plain English: "Show customer id and the sum of their order amounts (labelled total_spent), grouped by customer id."

Customer 2 is the biggest spender at 30,600 shillings. That's a real, actionable insight — and it came from two concepts (SUM + GROUP BY) you now understand.

Sorting the summary

You can even combine this with last post's ORDER BY to rank the groups:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Now customer 2 is on top, then 1, 4, and 3. Your top customers, ranked, in one query. This is genuinely the kind of question businesses pay good money to answer.

HAVING: filtering groups

One more piece. What if you only want groups that meet a condition — say, only customers who've spent more than 15,000? You can't use WHERE here, because WHERE filters rows before they're grouped. You need HAVING, which filters groups after they're formed:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 15000;

Plain English: "Show customer id and total spent, grouped by customer, but only keep groups whose total spent is greater than 15,000."

The result drops customers 3 and 4, leaving only the two big spenders.

The rule of thumb to remember: WHERE filters rows, HAVING filters groups. You'll rarely need HAVING early on, but it's good to know the difference — it's one of the most common points of confusion.

A fuller example: average salary by department

Let's return to employees and ask something a finance lead would genuinely care about: what's the average salary in each department?

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Now the HR lead can see at a glance which teams pay above or below the average. Engineering averages ~196,000; Customer Success averages 65,000. That's a whole compensation review, summarized in four rows.

And if she wants just the departments averaging over 130,000:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 130000;

Engineering and Finance remain; Sales and Customer Success drop off.

The full toolkit, one more time

Here's the master skeleton, now grown to its complete form:

SELECT <columns or aggregates>
FROM <table>
WHERE <row filter>
GROUP BY <grouping column>
HAVING <group filter>
ORDER BY <sort>
LIMIT <how many>;

That's essentially all of everyday SQL in one shape. You can pull columns, filter rows, group them, filter the groups, sort them, and trim them. The rest of the language is built on these same bones.

Try it yourself

On sqlbolt.com, work through the "aggregate functions" lessons. Then for our tables:

1. How many employees are there? (COUNT)
2. What's the total monthly salary bill? (SUM)
3. What's the average salary across the whole company? (AVG)
4. What's the highest and lowest salary? (MIN, MAX)
5. How many employees are in each department? (GROUP BY)
6. Which departments have more than one employee? (HAVING)

Next up, we'll tackle the thing that makes databases truly powerful — and genuinely confusing at first: combining multiple tables with JOINs. It's the bridge between "data in one place" and "the whole picture."