Syntax and usage of sum function

1. Sum function syntax:

SELECT SUM (expression) FROM tables WHERE predicates

Expression constants, columns, or functions, or any combination of operators such as arithmetic, bitwise, and string.

Expression is an expression that classifies exact or approximate numeric data types (except for bit data types).

Aggregate functions and subqueries are not allowed.

2. When using the SUM function, you should pay attention to the following points:

(1) the fields with indexes can speed up the operation of the aggregate function.

(2) only fields with field data types of int, smallint, tinyint, decimal, numeric, float, real, money and smallmoney can use the SUM function.

(3) when using the SUM function, SQL Server treats data types such as smallint or tinyint in the result set as int.

(4) when using the SUM function, SQL Server ignores null values (NULL), that is, these null values are not calculated.

3. Examples:

1. Statistical value column_name, constant

For example, count the total salaries of all employees with a salary of more than 25000 yuan

SELECT SUM (salary) as "all amount" from table where salary > 25000

For example, count the total salary of all employees who earn more than 25000 yuan and have no duplicate salary.

SELECT SUM (distinct salary) as "all amount" from table where salary > 25000

Superimposed use of sum functions

ELECT SUM (quantity) AS total quantity of goods sold, SUM (amount) AS total amount of goods sold FROM sales table

2. The value of the statistical expression formula (the result needs to be further calculated)

Select sum (case function) from table where condition.