SQL Server SUM Function

SUM to the summation. SUM return the summation of all values of a particular column. SUM ignore null values , but it can work on distinct value. SUM also work on summation of more than one columns in a query. SUM works on group by , partition over. SUM works on integer,numeric,decimal values only,SUM does not work on text or string and date time also.

We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

       id INT NOT NULL,

       name VARCHAR (500) NOT NULL,

       age INT NOT NULL,

       adress VARCHAR (500) NOT NULL,

       date_of_birth DATETIME NOT NULL,

       height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'44,student street','11/08/2018',16

Here is example of simple SUM function.
Example 1

SELECT sum(age)

FROM student

Result : 147 

Here is example of simple SUM function with more than one column.
Example 2

SELECT sum(age+height)

FROM student

Result : 293.00

Here is example of simple SUM function with DISTINCT.

Example 3

SELECT sum(DISTINCT age)

FROM student

Result : 70

Here is example of simple SUM function does not work with string values. 

Example 4

SELECT sum(name)

FROM student

Result : Operand data type varchar is invalid for sum operator.

Here is example of simple SUM function does not work with date values. 

Example 5

SELECT sum(date_of_birth)

FROM student

Result :Operand data type varchar is invalid for sum operator.

Here is example of simple SUM function work with Group by. 

Example 6

SELECT age as age_group,sum(height) AS number_of_count

from student

GROUP BY age

age_group number_of_count

12        49.50

13        36.50

14        14.50

15        29.50

16        16.00

Here is example of simple SUM function work with PARTITION OVER.

Example7

SELECT DISTINCT age as age_group,

sum(height) OVER (PARTITION BY age) AS height

FROM student

age_group number_of_count

12        49.50

13        36.50

14        14.50

15        29.50

16        16.00