SQL SUM Function

The SQL SUM Function is an Aggregate Function used to calculate the total or Sum of records (or rows) selected. The syntax of the SQL Server Sum Function is

SELECT SUM ([Column_Name])
FROM [Source]

For example, If you want to find the total price of products present in your Store. Or you want to find the total price of Black color products present in your Store. You can use the SQL Server Sum function. For this SQL Server Sum Function example, use the below data

SQL Sum Function 1

SQL SUM Example

The Aggregate Sum in Sql Server returns the sum of the total number of records present in the specified column. For example, the following query will calculate the sum of total records present in the [Yearly Income] column from the Customer table. The SUM Function will only work on Numeric Columns.

-- SQL Server SUM Example
USE [SQL Tutorial]
GO
SELECT SUM([YearlyIncome]) AS [Total Income]

FROM [Customer]

Execute the sum query

Results
-------
TotalIncome
-----------
1065000

SQL SUM Multiple columns

The sum function allows you to perform on multiple columns in a single select statement. In this Sql Server sum example, we are finding the sum of Sales and the Yearly Income

-- SQL Server SUM Example
SELECT SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
Results
-------
Total Income | Total Sales
--------------------------
1065000      | 28882.6782  

SQL SUM Group By Clause

In most cases, we usually use the SQL Server Sum function to find the sum of product sale belongs to a particular category or color. If you try to combine the Aggregate and non-aggregated columns in the select statement, then the SQL Server will throw an error

-- SQL Server SUM Example
SELECT Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]

Error: Column ‘Customer.Occupation’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SQL Sum Function 4

We use GROUP BY Clause to group the products by color or category. Then use the Sum Function to Sum the products present in each group. Let us see the Example of Sum function with Group By

-- SQL Server SUM Example
SELECT Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Occupation

It finds the Customers associated with the particular Department and total their income & total sales

SQL Sum Function 5

SQL SUM Distinct keyword

The SQL Server SUM DISTINCT Column calculates the sum of the Unique number of records present in a table whose values are NOT NULL (Ignores the NULL Records).

TIP: The SQL DISTINCT Keyword is used to remove the Duplicates from the specified column.

-- Example for SQL Server SUM Function
SELECT Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(DISTINCT [YearlyIncome]) AS [Distinct Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Occupation

Distinct Income column will Sum the Unique number of records (by removing duplicates) present in the [Yearly Income] column whose values are NOT NULL

SQL Sum Function 6

SQL SUM Order By Clause

You can use this Aggregate function called SQL Server SUM in Order By Clause. The below query will find the sum of Yearly income, Sales grouped by the Education and occupation Column. Next, Order By Clause will order those query result based on the Sum of yearly income in descending order.

-- Example for SQL Server SUM Function
SELECT Education
 ,Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Education, Occupation
ORDER BY SUM([YearlyIncome]) DESC
SQL Sum Function 7

SQL SUM Where Clause

We are using Where Clause along with the Sum function in Sql Server to display the sales greater than 100

-- Example for SQL Server SUM Function
SELECT Education
 ,Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
WHERE Sales > 100
GROUP BY Education, Occupation
ORDER BY SUM([YearlyIncome]) DESC
SQL Sum Function 8

SQL Sum Having Clause

In real-time, we might check for the conditions against the aggregated data. To do this, use the HAVING Clause along with Group By Statement.

The following SQL Server sum query will group the Customers by their Education and Occupation. Then it finds the Total Income and Sales.

-- Example for SQL Server SUM Function
SELECT Education
 ,Occupation
 ,SUM([YearlyIncome]) AS [Total Income]
 ,SUM(Sales) AS [Total Sales]
FROM [Customer]
GROUP BY Education, Occupation
HAVING SUM([YearlyIncome]) > 60000
ORDER BY SUM(Sales) DESC

It checks whether the aggregated amount (Sum of Yearly income of each individual Group) is greater than 60000 or not. If this is True, the corresponding records will display.

HAVING SUM ([YearlyIncome]) > 60000
SQL Sum Function 9