SQL SUM Function : Syntax & Examples Explained

Published : 15 July 2024
Updated : 16 July 2024

178 Views

#sql-aggregate-function

SUM function is a aggregate function that is designed to compute the sum or total of values within a specified column or expression. SUM operates on numeric data types (integer, decimal, floating-point) and it is particularly useful for performing aggregate calculations across datasets. This article provides a detailed overview of the SUM aggregate function with example. After reading this article you can use the SUM function easily and retrieve valuable data from a table.

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

SELECT SUM(expression)
FROM table_name
WHERE condition;

Demo Database

CREATE TABLE `Orders` (
   `Id` int PRIMARY KEY AUTO_INCREMENT,
   `CustomerId` INT NOT NULL,
   `ProductId` INT NOT NULL,
   `Quantity` INT NOT NULL,
   `Status` ENUM('pending', 'shipped', 'delivered', 'cancelled')
 );

INSERT INTO Orders (CustomerId, ProductId, Quantity, Status)
VALUES (1, 11, 1, "pending"),
(1, 12, 3, "delivered"),
(2, 11, 5, "pending"),
(1, 15, 2, "shipped"),
(1, 11, 7, "pending"),
(2, 11, 4, "delivered"),
(3, 12, 1, "cancelled");

SUM Example

SUM is a aggregate function, used to get the sum or total of values within a specified column or expression.

SQL query to get total Quantity of orders.

SELECT SUM(Quantity)
FROM Orders;

SUM(Quantity)
23

SUM with Expression

If you want to calculate the sum of an expression in SQL, you can directly use the expression within the SUM function.

SQL query to get normalized Quantity ( divide by 10) of orders.

SELECT SUM(Quantity/10)
FROM Orders;

SUM(Quantity/10)
2.3000

Using an Alias with SUM

you can provide a column name of SUM column using AS keyword.

SELECT SUM(*) AS [sum of records]
FROM Products;

SQL query to get total Quantity of orders table with summation column alias `TotalQuantity`

SELECT SUM(Quantity) AS TotalQuantity
FROM Orders;
    OR
SELECT SUM(Quantity) TotalQuantity
FROM Orders;

TotalQuantity
23

SUM with WHERE clause

If you want to calculate sum that satisfied certain condition( business logic). You can use SUM with WHERE clause.

SQL query to calculate sum of quantity where order status is pending. i.e. get the quantity of order which order status is pending.

SELECT SUM(Quantity) AS Quantity
FROM Orders
WHERE Status="pending";

Quantity
13

SUM with GROUP BY

It is useful, if you want to calculate the sum of values within groups defined by one or more columns. This is useful for generating aggregated results for different categories or dimensions.

SQL query that returns sum of quantity by grouping the results based on product ids.

SELECT ProductId, SUM(Quantity) as Quantity
FROM Orders
GROUP BY ProductId;

ProductId    Quantity
11   	      17
12	      4
15	      2

SQL query that returns sum of quantity by grouping the results based on product id and order status.

SELECT ProductId as pid, Status, SUM(Quantity) as Quantity
FROM Orders
GROUP BY ProductId, Status;

pid   Status    Quantity
11   pending    13
12   delivered  3
15   shipped    2
11   delivered  4
12   cancelled  1

SUM with GROUP BY and ORDER BY

You can calculate the sum of values within groups and then order the results based on specific criteria. You can order result set as ascending or descending order based on your business requirement.

SQL query that returns sum of quantity by grouping the results based on product id and order status and order result set based on product id.

SELECT ProductId as pid, Status, SUM(Quantity) as Quantity
FROM Orders
GROUP BY ProductId, Status
Order BY ProductId;

pid   Status    Quantity
11   pending    13
11   delivered  4
12   delivered  3
12   cancelled  1
15   shipped    2

Conditional Summing

In SQL , Conditional summing involves calculating the sum of values based on specified conditions. This can be achieved using the CASE statement within the SUM function or conditional aggregation using SUM along with CASE WHEN statements.

SQL query to get total amount that customer id 1 spend on product 11 ,12 and 13 where price of 1 item of product 11 is 50 , 12 is 200 and 13 is 100.

SELECT SUM( 
CASE WHEN ProductId = 11 THEN Quantity*50
   WHEN ProductId = 13 THEN Quantity*100
   WHEN ProductId = 12 THEN Quantity*200
ELSE 0 END
) as Amount
FROM Orders
WHERE CustomerId =1;

Amount
1000

SQL query to get amount that customers spend on product 11 ,12 and 13 where price of 1 item of product 11 is 50 , 12 is 200 and 13 is 100. group the result based on customer , product and quantity and also order result first on customer after that product.

SELECT CustomerId cid, ProductId pid, Quantity, SUM( 
CASE WHEN ProductId = 11 THEN Quantity*50
   WHEN ProductId = 13 THEN Quantity*100
   WHEN ProductId = 12 THEN Quantity*200
ELSE 0 END
) as Amount
FROM Orders
GROUP BY ProductId, Quantity, CustomerId
ORDER BY CustomerId, ProductId;

cid     pid    Quantity    Amount
1	11	1	50
1	11	7	350
1	12	3	600
1	15	2	0
2	11	5	250
2	11	4	200
3	12	1	200

Key Characteristics of SUM functions

SUM function is used to compute the sum or total of values within a specified column or expression.
SUM is commonly used with the SELECT.
Using SUM with GROUP BY allows us to compute summation within groups defined by one or more columns. This is essential for generating aggregated statistics and summaries across various categories or dimensions.
The SUM functions are crucial functions of SQL for robust data management and analysis for generating reports, creating dashboards and deriving insights from data stored in SQL databases.
NULL Handling : By default SUM ignores NULL values.
SUM function can be combined with other SQL functions such as GROUP BY, HAVING and CASE expressions to perform complex calculations and derive meaningful insights from data.

Frequently Asked Questions (FAQs)

1. What does the SUM function do in SQL?

The SQL SUM() function is an aggregate function used to calculate the total of numeric values in a column or expression. It processes multiple rows and returns a single numeric result, commonly used in reporting, analytics, billing systems, dashboards and financial calculations.

2. Which data types are supported by the SUM function?

SUM works only with numeric data types such as INT, DECIMAL, FLOAT and DOUBLE. It cannot be used directly on text or date columns. The column or expression must evaluate to a numeric value.

3. How does SUM handle NULL values?

SUM ignores NULL values by default. Rows where the column is NULL do not affect the total. If all rows are NULL, SUM returns NULL. To treat NULL as zero, use COALESCE(column,0).

4. What is the difference between SUM(column) and SUM(expression)?

SUM(column) adds values directly from a column. SUM(expression) allows calculations inside SUM, such as multiplication or conditional logic, useful for real-world calculations like Quantity * Price.

5. Can SUM be used with the WHERE clause?

Yes. WHERE filters rows before SUM is applied, allowing totals to be calculated for specific conditions such as order status, customer or date range.

6. How does SUM work with GROUP BY?

With GROUP BY, SUM calculates totals per group instead of a single overall total. Each group defined in GROUP BY gets its own aggregated result.

7. Can SUM be combined with ORDER BY?

Yes. ORDER BY can be used to sort SUM results, typically after GROUP BY. This is useful for ranking, such as top-selling products or highest-spending customers.

8. What is conditional SUM in SQL?

Conditional SUM uses CASE WHEN inside SUM to apply business logic during aggregation. It is widely used for calculating categorized totals or applying conditional pricing.

9. Can SUM return zero?

Yes. SUM returns zero if all evaluated values are zero. If all values are NULL, it returns NULL. Use COALESCE(SUM(column),0) to guarantee a numeric result.

10. Can SUM be used with JOIN queries?

Yes. SUM is commonly used with JOINs, but improper joins can multiply rows and inflate totals. Correct join conditions and GROUP BY usage are essential.

11. Is SUM allowed in UPDATE or INSERT statements?

Yes. SUM can be used inside subqueries in UPDATE or INSERT statements, commonly for maintaining summary tables or running aggregation jobs.

12. Does SUM affect performance on large tables?

SUM is efficient, but performance depends on indexing, filtering and query design. Indexed numeric columns and selective WHERE clauses improve performance on large datasets.

13. What happens if SUM is used without GROUP BY?

Without GROUP BY, SUM returns a single row representing the total for the entire result set. Adding GROUP BY changes the output to one row per group.

14. Is SUM commonly used in real-world systems?

Yes. SUM is heavily used in finance, e-commerce, analytics, inventory systems, dashboards and monitoring tools. It is a core SQL aggregation function.

15. Is SUM an important topic for SQL interviews?

Yes. Interviewers frequently test SUM with GROUP BY, NULL handling, conditional aggregation, JOIN behavior and performance implications.