We use SUM Function to calculate the sum of values in a column. SUM is a aggregated function and generally we use it with GROUP BY Clause. But sometimes we need to calculate to sum of all values in a table.
You can find the article about GROUP BY Clause below.
“Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server”
Let’s make examples for a better understanding of SUM Function.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[MyTable] ([ID] INT IDENTITY, [Name] VARCHAR(20) NULL, [Age] INT NULL, [Department] VARCHAR(50) NULL, [Salary] INT NULL) INSERT [dbo].[MyTable] VALUES ('Hakan GURBASLAR',36,'Database Department',12000), ('Nurullah CAKIR',34,'Database Department',12000), ('Faruk ERDEM',27,'System Department',9000), ('Ogun Ozalp',27,'System Department',null), ('Tamer Erdogan',43,'System Department',14000) |
Calculate sum of all staff’s salary:
|
1 |
SELECT SUM(Salary) FROM [dbo].[MyTable] |
Group the staffs accoding to their department and calculate sum of each group’s salary:
|
1 2 |
SELECT [Department],SUM(Salary) FROM [dbo].[MyTable] GROUP BY [Department] |
We can use DISTINCT or ALL Clause with SUM Function. The default value is ALL.
If you want to calculate sum of distinct values in a dataset you can use DISTINCT Clause as follows. As you know there is two staff with the same salary in the table. First look at the all records in the table.
|
1 |
SELECT * FROM [dbo].[MyTable] |
Then Execute the previous query with the DISTINCT Clause as follows:
As a result, sum of Database Department’s Salary will return 12000, because there is two record with the same value in this group.
|
1 2 |
SELECT [Department],SUM (DISTINCT Salary) FROM [dbo].[MyTable] GROUP BY [Department] |
Finally, you can use SUM Function with OVER Clause. You can find necessary details in the article “OVER Clause in SQL Server(TSQL)“
