How to use SQL SUM

SQL SUM is one of SQL’s aggregate functions. It enables you to add values from selected tables and columns, which can help you perform important functions and reduce the work involved in handling complex data sets.

What is SQL SUM?

Performing calculations with records is one of the most important tasks for database admins who work with SQL. That makes SQL SUM an indispensable tool for data aggregations like calculating sales, determining trends and data patterns, and creating reports and analyses.

SQL SUM calculates the sum of records and returns it in a result table. When combined with SQL operators, SQL commands and other aggregate functions like MIN, MAX and AVG, it can be used to carry out complex calculations.

What is the syntax of SQL SUM?

The basic syntax of SQL SUM looks as follows:

SELECT  SUM(ColumnA)
FROM  TableA;

It uses the following parameters:

• SELECT: SQL SELECT specifies the columns whose values you want to add up.
• SUM: Directly follows SELECT and specifies that you want to take the sum of the indicated column. If you combine SUM(ColumnA) with AS you can name the column for the added up values in the results table.
• WHERE: If you add an SQL WHERE clause, you can specify conditions for which values will be added. SQL HAVING, SQL SELECT DISTINCT and SQL CASE can also be used to limit which values are added.

What is SUM used for?

SQL SUM is an indispensable aggregate function in SQL. It’s used wherever values, sets and other information needs to be analyzed or calculated. Some common use cases for it include:

• Calculating and analyzing sales or the performance of a company, project or campaign
• Calculating the average value of shopping carts, ratings, sales or conversions
• Evaluating transactions, accounts or bank transfer amounts
• Calculating production volume, inventory and delivery quantities

Examples of SQL SUM

Below we’ll look at three examples that show how SQL SUM works.

Calculating inventory

In this example we’ll determine how many products are currently in stock using the columns “Quantity” and “ProductNames” from a table called “Products”. We’ll use the keyword AS to show the inventory as its own column in the target table. We’ll use a WHERE clause to specify which product you want to determine the inventory for.

The code looks as follows:

SELECT  SUM(Quantity)  AS  Inventory
FROM  Products
WHERE  ProductName  =  'ProductName1';

Sales by region

In this example, we’ll calculate and group sales by city using the columns “Price” and “City” in a table called “Orders”.

The code looks as follows:

SELECT   City,  SUM(Price)  AS Sales
FROM  Orders
GROUP BY  City;

Determining total salaries by department

In this example, we want to calculate and group the total salaries per department. We’ll use the columns “Name”, “Salary” and “Department” from a table called “Employees”. The code looks as follows:

SELECT   Department,  SUM(Salary)  AS Total_salary
FROM  Employees
GROUP BY  Department;

What are the alternatives to SQL SUM?

In addition to SQL SUM, there are a few other functions for performing calculations of records and numerical values:

• AVG: SQL AVG allows you to calculate the average value of columns.
• COUNT: SQL COUNT counts the number of records or rows in a table or column.
• MIN und MAX: SQL MIN AND MAX determine the smallest and largest values in the selected records.