How SUM works in SQL? Best SUM examples

Working with databases often involves performing calculations on numerical data. One of the most useful aggregate functions in SQL for this purpose is SUM(). It allows us to get the total sum of a column’s values, providing insightful summaries of data sets.

Understanding the SUM() Function

The SUM() function in SQL calculates the total of a specified column in a table. It is commonly used in financial transactions, sales reports, and statistical calculations to aggregate numerical data.

Its basic syntax looks like this:

SELECT SUM(column_name) 
FROM table_name 
WHERE condition;

Each part of this query plays a crucial role:

• SUM(column_name): Computes the total of the specified column.
• FROM table_name: Indicates which table contains the data.
• WHERE condition: (Optional) Filters rows before computation.

Basic Example of SUM() in SQL

Let’s consider a simple table named sales that records sales transactions.

id	product_name	quantity	price
1	Phone	2	500
2	Laptop	1	1200
3	Tablet	3	300

If I want to calculate the total revenue generated, I can use the SUM() function like this:

SELECT SUM(quantity * price) AS total_revenue 
FROM sales;

This query multiplies the quantity by the price for each row and then sums up the results. The output will be:

total_revenue
2900

Using SUM() with GROUP BY

The GROUP BY clause is often combined with the SUM() function to calculate totals per category.

If I want to view total sales revenue per product, I can use:

SELECT product_name, SUM(quantity * price) AS total_revenue 
FROM sales 
GROUP BY product_name;

The result will be:

product_name	total_revenue
Phone	1000
Laptop	1200
Tablet	900

Filtering Data Using SUM() and HAVING

When working with grouped data, the HAVING clause filters results after aggregation. Suppose I only want products generating revenue above 1000:

SELECT product_name, SUM(quantity * price) AS total_revenue 
FROM sales 
GROUP BY product_name 
HAVING total_revenue > 1000;

The result will be:

product_name	total_revenue
Phone	1000
Laptop	1200

Using SUM() with NULL Values

A common concern when using SUM() is dealing with NULL values. Fortunately, SUM() automatically ignores NULL values during calculations.

Consider a transactions table where some amounts are missing:

id	amount
1	200
2	NULL
3	500

If I run:

SELECT SUM(amount) AS total_amount 
FROM transactions;

The result will be:

total_amount
700

Key Takeaways

Here’s a summary of what I’ve covered about the SUM() function:

• It calculates the total sum of a specified column.
• It can be combined with GROUP BY to compute totals per category.
• HAVING helps filter results after aggregation.
• It automatically ignores NULL values.

Understanding SUM() helps in making smart data analyses, whether for financial reports, sales trends, or any numerical dataset.

 

Other interesting article:

How COUNT works in SQL? Best COUNT examples