SQL Server SUM()

SQL Server SUM() Function

The SQL Server SUM() function returns the sum of all or distinct values in an expression. This function is useful for calculating the total of numeric columns.

Syntax

SELECT SUM(expression)
FROM table_name
WHERE condition;

The SUM() function takes a single argument:

  • expression: The column or expression whose values are to be summed.

Example SQL Server SUM() Function Queries

Let's look at some examples of SQL Server SUM() function queries:

1. Basic SUM() Example

SELECT SUM(salary) AS total_salary
FROM employees;

This query returns the sum of all values in the salary column of the employees table. The result will be:

total_salary
------------
300000

2. SUM() with a Condition

SELECT SUM(salary) AS total_salary
FROM employees
WHERE department = 'Sales';

This query returns the sum of all values in the salary column for employees in the 'Sales' department. The result will be:

total_salary
------------
150000

3. SUM() with DISTINCT

SELECT SUM(DISTINCT salary) AS total_distinct_salary
FROM employees;

This query returns the sum of distinct values in the salary column of the employees table. The result will be:

total_distinct_salary
---------------------
280000

4. SUM() with a Column

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

This query returns the sum of the salary column for each department. The result will show the department and the corresponding total_salary.

5. SUM() with a Variable

DECLARE @dept VARCHAR(50);
SET @dept = 'Engineering';
SELECT SUM(salary) AS total_salary
FROM employees
WHERE department = @dept;

This query uses a variable to specify the department and then returns the sum of salaries for that department. The result will be:

total_salary
------------
130000

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SUM() function.

Step 1: Creating a Table

This step involves creating a new table named employees to store some sample data.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, name, department, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, name, department, salary) VALUES (1, 'John Doe', 'Engineering', 75000);
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Jane Smith', 'Sales', 85000);
INSERT INTO employees (id, name, department, salary) VALUES (3, 'Jim Brown', 'Engineering', 55000);
INSERT INTO employees (id, name, department, salary) VALUES (4, 'Jake White', 'Sales', 65000);
INSERT INTO employees (id, name, department, salary) VALUES (5, 'Jill Green', 'HR', 20000);

Here, we insert data into the employees table.

Step 3: Using the SUM() Function

This step involves using the SUM() function to return the total salary of the salary column.

SELECT SUM(salary) AS total_salary
FROM employees;

This query retrieves the total salary for all employees. The result will be:

total_salary
------------
300000

Conclusion

The SQL Server SUM() function is a powerful tool for returning the sum of all or distinct values in an expression. Understanding how to use the SUM() function and its syntax is essential for effective data analysis and processing in SQL Server.