Hello Reader,

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 (like sales analytics using SQL). 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: ~200 bookings, over $516K revenue
• Photography: ~253 bookings, over $494K revenue
• Safari: ~243 bookings, over $454K revenue
• Hiking: ~157 bookings, over $439K revenue
• Climbing: ~177 bookings, over $395K revenue

Photography leads in booking volume but cultural leads in revenue. That means cultural trips have a higher average transaction value (e.g., 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;

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
GROUPBY category_column
ORDERBY 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

Brian Graves, creator of Analytics in Action Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

​

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.

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.