What is an aggregate function, and what are some common examples?

An aggregate function in SQL is a function that performs a calculation on a set of values and returns a single value. Aggregate functions are often used with the GROUP BY clause of the SELECT statement to summarize data by group.

Some common examples of aggregate functions in SQL include:

COUNT(): Returns the number of rows in a table or column.

SUM(): Returns the sum of all the values in a column.

AVG(): Returns the average of all the values in a column.

MIN(): Returns the minimum value in a column.

MAX(): Returns the maximum value in a column.

Here is an example of an aggregate function used with the GROUP BY clause:

SQL

SELECT country, COUNT(*) AS num_customers

FROM customers

GROUP BY country;

Use code with caution. Learn more

This query will return the number of customers in each country.

Aggregate functions are a powerful tool for summarizing data in SQL. By understanding how to use aggregate functions, you can write more efficient and informative queries.

Here are some additional examples of how to use aggregate functions in SQL:

Calculate the total sales for each product:

SQL

SELECT product_name, SUM(sales) AS total_sales

FROM orders

GROUP BY product_name;

Use code with caution. Learn more

Calculate the average age of employees in each department:

SQL

SELECT department_name, AVG(age) AS average_age

FROM employees

GROUP BY department_name;

Use code with caution. Learn more

Find the employee with the highest salary:

SQL

SELECT name, salary

FROM employees

ORDER BY salary DESC

LIMIT 1;

Use code with caution. Learn more

Find the number of employees who have worked at the company for more than 5 years:

SQL

SELECT COUNT(*) AS num_employees

FROM employees

WHERE years_employed > 5;

Use code with caution. Learn more