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

SQL Server AVG Function

AVG function calculate average of data.A set of data can be average by AVG ,average can be done with group.


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

AVG work on numeric , decimal, integer values but does not work string of text value  or date value. You can apply distinct in average function. Average function work with group by and partition by also.

Here is example of simple AVG. 
 
Example 1

SELECT avg(age)

FROM student

Result : 13

Here is example of simple AVG with distinct value. 

Example 2

SELECT avg(DISTINCT age)

FROM student

Result : 14


Here is example of AVG cannot be used with string data type. 
Example 3

SELECT avg(name)

FROM student

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

Here is example of AVG cannot be used with date data type. 
Example 4 

SELECT avg(date_of_birth)

FROM student

Result : Operand data type datetime is invalid for avg operator.

Here is example of AVG with group by . 
Example 4 

         SELECT age as age_group,avg(height) AS number_of_count

         from student

         GROUP BY age

age_group number_of_count

12        12.375000

13        12.166666

14        14.500000

15        14.750000

16        16.000000

Here is example of AVG with Partition by . 
Example 5
 

SELECT DISTINCT age as age_group,

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

FROM student

age_group number_of_count

12        12.375000

13        12.166666

14        14.500000

15        14.750000

16        16.000000