SQL COUNT(), AVG() and SUM() Functions
SQL COUNT(), AVG() and SUM() Functions
In SQL, the COUNT(), AVG(), and SUM() functions are used to perform aggregate calculations on a set of values in a table.
The COUNT() function returns the number of rows that match a specified condition.
The AVG() function returns the average (mean) of the values in a specified column.
The SUM() function returns the sum of the values in a specified column.
Here is the basic syntax for using these functions:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
In these statements, column_name is the name of the column on which we want to perform the aggregate calculation, and table_name is the name of the table containing the column. The condition is an optional parameter that can be used to specify a filtering criterion for the rows in the table.
Let’s take a look at an example:
Suppose we have a table called sales that contains data about the sales made by a company. The table has three columns: id, product, and price. We want to retrieve the total number of sales made, the average price of a sale, and the total revenue generated.
SELECT COUNT(*) AS total_sales, AVG(price) AS average_price, SUM(price) AS total_revenue
FROM sales;
This query will return a result set with three columns: total_sales, average_price, and total_revenue, containing the total number of sales made, the average price of a sale, and the total revenue generated, respectively.
We can also use the WHERE clause to filter the results:
SELECT COUNT(*) AS total_sales, AVG(price) AS average_price, SUM(price) AS total_revenue
FROM sales
WHERE product = 'Widget';
This query will return the total number of Widget products sold, the average price of a Widget sale, and the total revenue generated from Widget sales.
The COUNT(), AVG(), and SUM() functions can be used in combination with other SQL functions and clauses, such as GROUP BY, HAVING, and ORDER BY, to perform more complex queries on the data.