SQL - COUNT(), SUM() & AVG() Functions
The SQL COUNT() function is used to return the number of rows satisfies the specified condition.
The SQL SUM() function is used to return the sum of a numeric column which satisfies the given criteria.
The SQL AVG() function is used to return the average value of a numeric column which satisfies the given criteria.
Syntax
The syntax for using COUNT(), SUM() and AVG() functions are given below:
/* SQL COUNT() Function */ SELECT COUNT(column_name) FROM table_name WHERE condition(s); /* SQL SUM() Function */ SELECT SUM(column_name) FROM table_name WHERE condition(s); /* SQL AVG() Function */ SELECT AVG(column_name) FROM table_name WHERE condition(s);
Example:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To count the number of records in the Employee table where age is greater than 25, the SQL code is:
SELECT COUNT(Name) AS EmployeeGT25 FROM Employee WHERE Age > 25;
This will produce the result as shown below:
EmployeeGT25 4 -
To find out the total salary paid to all employees with age greater than 25, the SQL code is:
SELECT SUM(Salary) AS TotalSalary_AgeGT25 FROM Employee WHERE Age > 25;
This result of the following code will be:
TotalSalary_AgeGT25 11700 -
To fetch the average salary of an employee with age greater than 25, the SQL code is:
SELECT AVG(Salary) AS AvgSalary_AgeGT25 FROM Employee WHERE Age > 25;
This result of the above code will be:
AvgSalary_AgeGT25 2925