SQL SUM(): Syntax, Usage, and Examples

The SUM() function adds up numeric values in a column and returns a single total. You’ll use it constantly for reporting, dashboards, analytics, and “how much did we sell?” questions.

How to Use the SUM() function

The syntax is simple, SUM() takes one numeric expression and returns the total.

SELECTSUM(column_name)
FROM table_name;

Most of the time, you’ll use it with a WHERE filter, a GROUP BY, or both.

Summing a column with a filter

SELECTSUM(amount)AS total_spent
FROM orders
WHERE customer_id=42;

That query totals all amount values for one customer.

Summing groups of rows

To get totals per category, per date, per user, and so on, pair SUM() with GROUP BY.

SELECT category,SUM(amount)AS category_total
FROM expenses
GROUPBY category;

Summing an expression

SUM() can also total calculated values. For example, revenue is often price * quantity.

SELECTSUM(price* quantity)AS total_revenue
FROM order_items;

When to Use the SUM() function

SUM() is perfect when you need totals instead of individual rows. Here are some common use cases you’ll run into a lot.

1) Getting a single total for a report

You might want the total revenue, total hours worked, or total distance traveled.

Examples:

• Total monthly sales
• Total support tickets handled by a team
• Total calories logged in a fitness app

2) Calculating totals per group

SUM() becomes even more useful when you want totals per segment.

Examples:

• Total sales per product
• Total views per country
• Total refunds per payment method

3) Building dashboards and KPIs

Dashboards often show numbers like “total revenue this week” or “total active minutes today.” Behind the scenes, that’s often SUM() with filters.

4) Doing comparisons over time

You can sum per day, per week, or per month, then compare results between periods.

Examples:

• Daily signups for the last 14 days
• Revenue per month for the last year
• Spend per campaign by week

Examples of the SUM() function

Let’s walk through a few clear, realistic examples.

Example 1: Total revenue from all orders

Imagine a table called orders:

• id
• amount
• created_at

SELECTSUM(amount)AS total_revenue
FROM orders;

Result: one row, one number.

Example 2: Total revenue for a specific month

SELECTSUM(amount)AS total_revenue
FROM orders
WHERE created_at>='2026-01-01'
AND created_at<'2026-02-01';

Using a date range like this tends to work well across SQL databases and avoids surprises with time values.

Example 3: Total spend per category

Let’s say you track expenses in an expenses table:

• category
• amount

SELECT category,SUM(amount)AS total_spent
FROM expenses
GROUPBY category
ORDERBY total_spentDESC;

You get a list of categories, plus the totals from highest to lowest.

Example 4: Total revenue per product

Imagine an order_items table:

• product_name
• price
• quantity

SELECT product_name,SUM(price* quantity)AS product_revenue
FROM order_items
GROUPBY product_name
ORDERBY product_revenueDESC;

This is a very common pattern in eCommerce analytics.

Example 5: Only sum rows that match a condition

Sometimes you only want to sum “completed” orders.

SELECTSUM(amount)AS completed_revenue
FROM orders
WHERE status='completed';

Learn More About the SUM() function

SUM() ignores NULL values

One thing that surprises beginners is that SUM() does not count NULL as zero. It just skips it.

So if a table contains:

• 10
• 15
• NULL

The sum is 25, not NULL, and not 0.

SELECTSUM(amount)AS total
FROM orders;

That’s usually what you want, but it’s good to know.

Using SUM() with COALESCE()

If your column can be NULL and you want to treat missing values as zero for clarity, use COALESCE().

SELECTSUM(COALESCE(amount,0))AS total
FROM orders;

This can also make your intent clearer when someone else reads the query.

SUM() with JOINs

SUM() is often used after joining tables, for example when you need totals per customer.

SELECT c.name,SUM(o.amount)AS total_spent
FROM customers c
JOIN orders oON o.customer_id= c.id
GROUPBY c.name
ORDERBY total_spentDESC;

This answers “Who spent the most?” in one query.

SUM() vs COUNT()

These two are often confused early on:

• COUNT() tells you how many rows exist.
• SUM() adds up numeric values.

Example:

SELECT
COUNT(*)AS order_count,
SUM(amount)AS total_revenue
FROM orders;

That gives you both “how many” and “how much.”

SUM() with DISTINCT

Sometimes you want to add only distinct values. This is less common, but it exists.

SELECTSUM(DISTINCT amount)AS distinct_total
FROM refunds;

Be careful with this one. It can hide duplicates that you actually needed to count.

SUM() with CASE for conditional totals

This is one of the most useful patterns for reporting.

Example: total revenue split by paid vs free orders.

SELECT
SUM(CASEWHEN payment_status='paid'THEN amountELSE0END)AS paid_total,
SUM(CASEWHEN payment_status='free'THEN amountELSE0END)AS free_total
FROM orders;

You can also use it for totals per region, per team, per anything.

Filtering after GROUP BY using HAVING

When you group results, WHERE filters rows before grouping. HAVING filters groups after the totals exist.

Example: show only categories with more than 1,000 in total spend.

SELECT category,SUM(amount)AS total_spent
FROM expenses
GROUPBY category
HAVINGSUM(amount)>1000
ORDERBY total_spentDESC;

This is the “only show big categories” query.

Watch out for accidental double counting

If you join the wrong way, you can multiply rows and inflate the sum.

Example: joining a customer table to both orders and messages can produce duplicates if messages multiply rows per order.

A quick sanity check:

• Run the join without SUM() first
• Check the row count
• Confirm each order appears once (or the correct number of times)

When totals look too high, joins are often the culprit.

SUM() in window functions

Some databases support window functions, which let you show totals without collapsing rows.

Example: show each order plus the total spend for that customer.

SELECT
  customer_id,
  amount,
SUM(amount)OVER (PARTITIONBY customer_id)AS customer_total
FROM orders;

This is great for ranking, comparisons, and analytics views where you still want row-level detail.

Summary

SUM() adds up numeric values and returns totals that power most reports and analytics queries. Use it for overall totals, grouped summaries with GROUP BY, conditional totals with CASE, and time-based reporting with date filters.

Once you combine it with joins and HAVING, you can answer a lot of real-world business questions in a single query.