This SQL Server Aggregate Function calculates the total or Sum of records (or rows) selected, and the syntax of it is
SELECT SUM([Column_Name]) FROM [Source]
For example, If you want to find the total price of products present in your Store. If you’re going to find the full price of Black color products present in your Store, you can use this one. For this SQL Server Sum Function example, use the below data.
SQL Server SUM Function Example
The Sum function returns the aggregate of the total number of records 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 SQL Server Aggregate sum Function will only work on Numeric Columns.
SELECT SUM([YearlyIncome]) AS [Income] FROM [Customer]
Results
-------
Income
-----------
1065000How 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 Server sum function example, we find 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 Server SUM Function Group By Clause
In most cases, we usually use this SQL Server 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.
We use the GROUP BY Clause to group the products by color or category. Then use this aggregate Function to find each Group’s total sales and income. 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 totals their income & total sales.
SQL Server SUM Distinct Function
The 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 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
The distinct Income column will add the Unique number of records (by removing duplicates) present in the [Yearly Income] Column whose values are NOT NULL
SUM Order By Clause
You can use this SQL Server sum function in Order By Clause. For example, 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 results based on the total yearly income in descending.
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 Server Sum Function Where Clause Example
We are using the Where Clause along with the Sum function 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
In real time, we might check for the conditions against the aggregated data. To do this, use the HAVING Clause and Group By Statement in 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.
