SQL SUM Function

The SQL SUM Function is one of the Aggregate Functions 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 function. For this SQL Sum Function examples, use the below data

Customer Table

SQL SUM Function Example

The Aggregate Sum function in Sql Server returns the aggregate 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. This sum Function will only work on Numeric Columns.

SELECT SUM([YearlyIncome]) AS [Income]

FROM [Customer]
Results
-------
Income
-----------
1065000

How to Find the Sum of Multiple columns?

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

SELECT SUM([YearlyIncome]) AS [Earning]
 ,SUM(Sales) AS [Sales]
FROM [Customer]
Results
-------
Earning | Sales
--------------------------
1065000 | 28882.6782  

SQL SUM Group By Clause

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

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.

Aggregate Function Errors

We use GROUP BY Clause to group the products by color or category. Then use this agg Function to find the total sakes and income present in each group. Let us see the Example of it with Group By

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 GROUP BY Clause

SQL SUM Distinct Function

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

TIP: The DISTINCT Keyword is used to remove the Duplicates from the specified column and returns distinct values.

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 add the Unique number of records (by removing duplicates) present in the [Yearly Income] column whose values are NOT NULL

SQL SUM DISTINCT Function

SQL SUM Order By Clause

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

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 Order By Clause

SUM Where Clause Example

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

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 and Where 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 in SQL Sum.

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

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

SUM Where Clause Example