SQL provides several aggregate functions that allow you to perform calculations on a set of records. The COUNT(), AVG(), and SUM() functions are some of the most commonly used aggregate functions in SQL.
SQL COUNT() Function
The COUNT() function is used to count the number of rows in a table. The syntax of the COUNT() function is as follows:
SELECT COUNT(column_name) FROM table_name;
Where “column_name” is the name of the column that you want to count the number of rows for, and “table_name” is the name of the table that contains the column.
For example, consider the following “employees” table:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | | 3 | Bob Smith| 45000 | +----+----------+--------+
To count the number of employees in the “employees” table, you would run the following query:
SELECT COUNT(ID) FROM employees;
This would return the following result:
+----------+ | COUNT(ID)| +----------+ | 3| +----------+
SQL AVG() Function
The AVG() function is used to find the average of a set of values in a column. The syntax of the AVG() function is as follows:
SELECT AVG(column_name) FROM table_name;
Where “column_name” is the name of the column for which you want to find the average value, and “table_name” is the name of the table that contains the column.
For example, consider the following “employees” table:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | | 3 | Bob Smith| 45000 | +----+----------+--------+
To find the average salary of employees in the “employees” table, you would run the following query:
SELECT AVG(Salary) FROM employees;
This would return the following result:
+-----------+ | AVG(Salary)| +-----------+ | 50000.0| +-----------+
SQL SUM() Function
The SUM() function is used to find the sum of a set of values in a column. The syntax of the SUM() function is as follows:
SELECT SUM(column_name) FROM table_name;
Where “column_name” is the name of the column for which you want to find the sum, and “table_name” is the name of the table that contains the column.
For example, consider the following “employees” table:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | | 3 | Bob Smith| 45000 | +----+----------+--------+
To find the sum of salaries of employees in the “employees” table, you would run the following query:
SELECT SUM(Salary) FROM employees;
This would return the following result:
+-----------+ | SUM(Salary)| +-----------+ | 150000 | +-----------+
In conclusion, the COUNT(), AVG(), and SUM() functions in SQL provide an easy and efficient way to perform aggregate calculations on data stored in a database. These functions are commonly used in data analysis and reporting, and are a fundamental part of SQL.
