Aggregate Function in SQL

In SQL, aggregate functions are operations that take a set of values and produce a single output. These procedures can be used to calculate the average, sum, minimum, maximum, or count of a certain column in a table, among other mathematical operations on a set of numbers.

Some commonly used aggregate functions in SQL include:

1. COUNT(): This function is used to count the number of rows in a table or the number of non-null values in a column.

2. SUM(): This function is used to calculate the sum of all values in a column.

3. AVG(): This function is used to calculate the average value of a column.

4. MAX(): This function is used to find the maximum value in a column.

5. MIN(): This function is used to find the minimum value in a column.

To perform calculations and provide summary data from a table, aggregate functions can be used along with the SELECT command.

Examples of Aggregate Functions in SQL:

1. COUNT(): To count the total number of employees in a table called "employees": SELECT COUNT(*) FROM employees;

2. SUM(): To calculate the total salary of all employees in the "employees" table: SELECT SUM(salary) FROM employees;

3. AVG(): To calculate the average salary of employees in the "employees" table: SELECT AVG(salary) FROM employees;

4. MAX(): To find the highest salary in the "employees" table: SELECT MAX(salary) FROM employees;

5. MIN(): To find the lowest salary in the "employees" table: SELECT MIN(salary) FROM employees;

In order to group the data by one or more columns and carry out calculations on each group independently, aggregate functions can also be utilised with the GROUP BY clause. To figure out the total compensation for each department in the "employees" table, for instance: department, workers grouped by department, SUM(salary); A result set with the two columns "department" and "SUM(salary)" will be returned, with each row representing a department and its accompanying total salary.

Here are some exercises for to practice using aggregate functions in SQL.

Consider the following table "sales" with the following columns:

• id (int)
• customer_name (varchar)
• product_name (varchar)
• price (int)
• sale_date (date)

1. Write an SQL query to find the total number of sales in the "sales" table.

      SELECT COUNT(*) FROM sales;

2. Write an SQL query to find the total revenue generated by all sales in the "sales" table.

      SELECT SUM(price) FROM sales;

3. Write an SQL query to find the average price of products sold in the "sales" table.

SELECT AVG(price) FROM sales;

4. Write an SQL query to find the highest price of a product sold in the "sales" table.

SELECT MAX(price) FROM sales;

5. Write an SQL query to find the number of sales made by each customer in the "sales" table.

SELECT customer_name, COUNT(*) FROM sales GROUP BY customer_name;

6. Write an SQL query to find the total revenue generated by each product sold in the "sales" table.

SELECT product_name, SUM(price) FROM sales GROUP BY product_name;

These exercises can serve as a solid starting point for using SQL's aggregate functions. To improve your abilities and familiarity with SQL, keep practicing.