T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - COUNT(), SUM() & AVG() Functions



The T-SQL (Transact-SQL) COUNT() function returns the count of an expression.

The T-SQL (Transact-SQL) SUM() function returns the summed value of an expression.

The T-SQL (Transact-SQL) AVG() function returns the average value of an expression.

T-SQL COUNT() Function

The T-SQL (Transact-SQL) COUNT() function returns the count of an expression.

Syntax

The syntax for using COUNT() function in T-SQL (Transact-SQL) is given below:

SELECT COUNT(column_name) 
FROM table_name
WHERE condition(s);

The T-SQL GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the COUNT() function. See the syntax below:

SELECT column1, column2, ...
       COUNT(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To get the count of employees whose age is greater than 25, the following query can be used:

SELECT COUNT(Name) AS EmployeeGT25 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

EmployeeGT25
4


Example - Using DISTINCT

The DISTICT clause can be used with COUNT() function. For example - To get the count of distinct (unique) age of the employees, the following code can be used:

SELECT COUNT(DISTINCT Age) AS DistinctAge 
FROM Employee;

This will produce the result as shown below:

DistinctAge
5


Example - Using GROUP BY

To get the count of employees group by their age, the following query can be used:

SELECT Age, COUNT(Age) AS NumberOfEmployee 
FROM Employee
GROUP BY Age;

This result of the above code will be:

AgeNumberOfEmployee
241
251
271
282
301

T-SQL SUM() Function

The T-SQL (Transact-SQL) SUM() function returns the summed value of an expression.

Syntax

The syntax for using SUM() function in T-SQL (Transact-SQL) is given below:

SELECT SUM(column_name) 
FROM table_name
WHERE condition(s);

The T-SQL GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the SUM() function. See the syntax below:

SELECT column1, column2, ...
       SUM(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To get the combined total salary of all employees whose age is greater than 25, the following query can be used:

SELECT SUM(Salary) AS TotalSalary 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

TotalSalary
11700


Example - Using DISTINCT

The DISTICT clause can be used with SUM() function. For example - To get the combined total salary of distinct (unique) salaries, the following code can be used:

SELECT SUM(DISTINCT Salary) AS TotalUniqueSalary 
FROM Employee;

This will produce the result as shown below:

TotalUniqueSalary
11650


Example - Using Formula

The expression contained within the SUM() function does not need to be a single field. A formula can also be used with this function. For example - Consider a bonus (15% of Salary) is given to each employee, and to calculate the total bonus amount, the following query can be used:

SELECT SUM(Salary * 0.15) AS TotalBonusAmount
FROM Employee;

This will produce the result as shown below:

TotalBonusAmount
2617.5


Example - Using GROUP BY

To get the combined total salary of employees group by their age, the following query can be used:

SELECT Age, SUM(Salary) AS TotalSalaryByAge 
FROM Employee
GROUP BY Age;

This result of the above code will be:

AgeTotalSalaryByAge
242750
253000
272800
285800
303100

T-SQL AVG() Function

The T-SQL (Transact-SQL) AVG() function returns the average value of an expression.

Syntax

The syntax for using AVG() function in T-SQL (Transact-SQL) is given below:

SELECT AVG(column_name) 
FROM table_name
WHERE condition(s);

The T-SQL GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the AVG() function. See the syntax below:

SELECT column1, column2, ...
       AVG(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To get the average salary of employee with age greater than 25, the following query can be used:

SELECT AVG(Salary) AS AvgSalary 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

AvgSalary
2925.0


Example - Using DISTINCT

The DISTICT clause can be used with AVG() function. For example - To get the average value of distinct (unique) salaries, the following code can be used:

SELECT AVG(DISTINCT Salary) AS AvgUniqueSalary 
FROM Employee;

This will produce the result as shown below:

AvgUniqueSalary
2912.5


Example - Using Formula

The expression contained within the AVG() function does not need to be a single field. A formula can also be used with this function. For example - Consider a bonus (15% of Salary) is given to each employee, and to calculate the average of bonus amount, the following query can be used:

SELECT AVG(Salary * 0.15) AS AvgBonusAmount
FROM Employee;

This will produce the result as shown below:

AvgBonusAmount
436.25


Example - Using GROUP BY

To get the average salary of employees group by their age, the following query can be used:

SELECT Age, AVG(Salary) AS AvgSalaryByAge 
FROM Employee
GROUP BY Age;

This result of the above code will be:

AgeAvgSalaryByAge
242750.0
253000.0
272800.0
282900.0
303100.0