SUM in SQL

DevSniper
Published : 15 July 2024

173 Views

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.