SQL COUNT(), AVG() and SUM() are three commonly used aggregate functions in SQL.
These functions allow you to perform calculations on a set of values in a specific column of a table.
COUNT()
The COUNT() function is used to count the number of rows in a table or the number of non-NULL values in a specific column.
The syntax for using COUNT() is as follows:
SELECT COUNT(column_name) FROM table_name;
For example, if you have a table called “employees” and you want to count the number of rows in the table, you would use the following query:
SELECT COUNT(*) FROM employees;
If you want to count the number of employees in each department, you can use the COUNT() function with a GROUP BY clause:
SELECT department, COUNT(*) FROM employees GROUP BY department;
AVG()
The AVG() function is used to calculate the average value of a specific column.
The syntax for using AVG() is as follows:
SELECT AVG(column_name) FROM table_name;
For example, if you have a table called “orders” and you want to calculate the average price of all orders, you would use the following query:
SELECT AVG(price) FROM orders;
SUM()
The SUM() function is used to calculate the sum of values in a specific column.
The syntax for using SUM() is as follows:
SELECT SUM(column_name) FROM table_name;
For example, if you have a table called “sales” and you want to calculate the total sales for all products, you would use the following query:
SELECT SUM(sales) FROM products;
With GROUP BY clause
You can also use these aggregate functions in combination with a GROUP BY clause to perform calculations on subsets of your data.
For example, if you want to calculate the total sales for each product category, you can use the following query:
SELECT category, SUM(sales) FROM products GROUP BY category;
These are the basic syntax and examples of COUNT(), AVG() and SUM() functions.
There are many more ways to use these functions and many more functions to explore that can help you analyze and manipulate your data in various ways.