Hello ,
Almost every business question boils down to one of two things:
“How many?” (COUNT)
“How much?” (SUM)
How many customers booked this month? How much revenue did we generate? How many trips were cancelled? How much did we lose?
Once you’re comfortable with COUNT and SUM, you can answer the majority of questions that come your way. Let me show you both patterns using a real business scenario.
The Scenario
Your operations manager asks: “Give me a breakdown of our bookings and revenue by expedition type. I need to know which categories are driving volume and which are driving dollars.”
Two questions in one. COUNT answers the first. SUM answers the second.
The Query
Here’s what I ran against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):
-- Booking volume and revenue by expedition type
SELECT
e.expedition_type,
COUNT(DISTINCT b.booking_id) AS total_bookings,
COUNT(DISTINCT b.booking_id) FILTER (WHERE b.status = 'completed') AS completed_bookings,
SUM(p.amount) AS total_revenue,
ROUND(AVG(p.amount), 2) AS avg_transaction_value
FROM expeditions e
INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
INNER JOIN bookings b ON ei.instance_id = b.instance_id
INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
GROUP BY e.expedition_type
ORDER BY total_revenue DESC;
Summit Adventures results look roughly like this:
Cultural: ~280 bookings, over $370K revenue
Hiking: ~250 bookings, over $350K revenue
Photography: ~310 bookings, over $340K revenue
Safari: ~300 bookings, over $330K revenue
Climbing: ~270 bookings, over $300K revenue
The insight:
Photography leads in booking volume but cultural leads in revenue. That means cultural trips have a higher average transaction value — customers pay more per booking. This tells the operations team something important: cultural expeditions are premium products, while photography is a volume play.
Different strategies for different categories. One query reveals both.
COUNT: The Volume Function
COUNT answers “how many” questions:
-- How many customers do we have?
SELECT COUNT(*) AS total_customers FROM customers;
-- How many unique customers have booked?
SELECT COUNT(DISTINCT customer_id) AS unique_bookers FROM bookings;
-- How many customers in each experience level?
SELECT
experience_level,
COUNT(*) AS customer_count
FROM customers
GROUP BY experience_level;
Three forms of COUNT:
| Syntax | What It Counts | Use When |
|——–|—————|———-|
| `COUNT(*)` | Every row, including NULLs | Total record count |
| `COUNT(column)` | Non-NULL values only | Count where data exists |
| `COUNT(DISTINCT column)` | Unique values only | Unique customers, unique products |
The difference between `COUNT(*)` and `COUNT(DISTINCT column)` trips people up at first. Here’s the key:
If a customer made 5 bookings, `COUNT(*)` returns 5 (one for each booking row). `COUNT(DISTINCT customer_id)` returns 1 (one unique customer). Both are correct — they’re answering different questions.
SUM: The Value Function
SUM answers “how much” questions:
-- Total revenue
SELECT SUM(amount) AS total_revenue
FROM payments
WHERE payment_status = 'completed';
-- Revenue by payment method
SELECT
payment_method,
SUM(amount) AS method_revenue,
COUNT(*) AS transaction_count
FROM payments
WHERE payment_status = 'completed'
GROUP BY payment_method
ORDER BY method_revenue DESC;
SUM is straightforward, but there’s one important thing: Make sure you’re filtering correctly before summing. If you SUM all payments without filtering for `payment_status = ‘completed’`, you’ll include pending and failed payments in your revenue number. That’s a reporting mistake that can lead to bad decisions.
Using Them Together
The real power comes when you combine COUNT and SUM in the same query:
-- Customer spending summary
SELECT
c.experience_level,
COUNT(DISTINCT c.customer_id) AS customers,
COUNT(DISTINCT b.booking_id) AS bookings,
SUM(p.amount) AS total_revenue,
ROUND(SUM(p.amount) / COUNT(DISTINCT c.customer_id), 2) AS revenue_per_customer,
ROUND(SUM(p.amount) / COUNT(DISTINCT b.booking_id), 2) AS revenue_per_booking
FROM customers c
INNER JOIN bookings b ON c.customer_id = b.customer_id
INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
AND b.status IN ('completed', 'confirmed')
GROUP BY c.experience_level
ORDER BY revenue_per_customer DESC;
Now you can answer: “Which experience level has the most customers? Which generates the most revenue? Which has the highest value per customer?”
These are three different questions from one query. COUNT gives you volume. SUM gives you value. Dividing SUM by COUNT gives you efficiency.
Two Common Mistakes
Mistake 1: Counting when you should be summing
“How much revenue did we make?” → Use SUM(amount), not COUNT(*)
COUNT(amount) tells you how many payment records exist. SUM(amount) tells you how much money came in. Very different answers.
Mistake 2: Forgetting DISTINCT
If you JOIN customers to bookings to payments, a customer with 3 bookings appears 3 times. `COUNT(customer_id)` returns 3. `COUNT(DISTINCT customer_id)` returns 1.
When you want “how many unique customers,” always use DISTINCT. When you want “how many transactions,” regular COUNT is correct.
The Pattern to Remember
SELECT
category_column,
COUNT(DISTINCT id_column) AS volume_metric,
SUM(value_column) AS value_metric,
ROUND(SUM(value_column) / COUNT(DISTINCT id_column), 2) AS efficiency_metric
FROM your_tables
WHERE your_filters
GROUP BY category_column
ORDER BY value_metric DESC;
This three-metric pattern (volume + value + efficiency) answers almost any business performance question. Use it with different category columns — by region, by product, by time period, by team — and you’ve got a flexible analytical tool.
Try This At Your Job
Next time your manager asks about business performance, structure your answer around these three questions:
1. How many? (COUNT) — Volume, activity, engagement
2. How much? (SUM) — Revenue, cost, quantity
3. How efficiently? (SUM ÷ COUNT) — Per-unit performance
“We had 280 cultural bookings (volume) generating over $370K (value), averaging about $1,300 per booking (efficiency).”
That’s a complete answer from two SQL functions.
Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))
—
P.S. Understanding when to use COUNT vs SUM is one of those skills that seems simple but makes a massive difference in daily work. Module 6 of SQL for Business Impact goes deep on revenue analysis patterns using these functions and the So What Framework. Check it out at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).
P.P.S. What’s the most common “how many” or “how much” question you get asked at work? Reply and share it — I might turn it into a future SQL walkthrough. I read every response.
Want More SQL Insights Like This?
Join thousands of analysts getting weekly tips on turning data into business impact.