SQL Interview Questions

Aggregate functions in SQL are functions that perform a calculation on a set of values and return a single value. These functions are commonly used in conjunction with the GROUP BY clause to perform calculations on groups of rows.

Common Aggregate Functions

1. SUM(): Calculates the total sum of a numeric column.
2. AVG(): Calculates the average value of a numeric column.
3. COUNT(): Counts the number of rows in a result set.
4. MAX(): Returns the maximum value in a set of values.
5. MIN(): Returns the minimum value in a set of values.

Examples of Aggregate Functions

1. SUM()

The SUM() function calculates the total sum of a numeric column.

Example: Calculate the total sales amount from the sales table.

SELECT SUM(sale_amount) AS total_sales
FROM sales;

2. AVG()

The AVG() function calculates the average value of a numeric column.

Example: Calculate the average salary of employees from the employees table.

SELECT AVG(salary) AS average_salary
FROM employees;

3. COUNT()

The COUNT() function counts the number of rows in a result set.

Example: Count the number of employees in the employees table.

SELECT COUNT(*) AS total_employees
FROM employees;

Example: Count the number of distinct job titles in the employees table.

SELECT COUNT(DISTINCT job_title) AS unique_job_titles
FROM employees;

4. MAX()

The MAX() function returns the maximum value in a set of values.

Example: Find the highest salary in the employees table.

SELECT MAX(salary) AS highest_salary
FROM employees;

5. MIN()

The MIN() function returns the minimum value in a set of values.

Example: Find the lowest sale amount in the sales table.

SELECT MIN(sale_amount) AS lowest_sale
FROM sales;

Using Aggregate Functions with GROUP BY

Aggregate functions are often used with the GROUP BY clause to perform calculations on groups of rows.

Example: Calculate the total sales for each region from the sales table.

SELECT region, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY region;

Example: Calculate the average salary for each department from the employees table.

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Combining Aggregate Functions

You can combine multiple aggregate functions in a single query to perform different calculations on the same set of data.

Example: Calculate the total, average, maximum, and minimum salary from the employees table.

SELECT 
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

Using Aggregate Functions with HAVING Clause

The HAVING clause is used to filter groups based on the result of an aggregate function. It is similar to the WHERE clause but is used for groups.

Example: Find departments with an average salary greater than 60,000.

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Conclusion

Aggregate functions in SQL are essential for performing calculations on data sets and summarizing information. By using functions like SUM(), AVG(), COUNT(), MAX(), and MIN(), you can derive meaningful insights from your data. Combining these functions with GROUP BY and HAVING clauses allows for powerful and flexible data analysis capabilities. Understanding and leveraging aggregate functions is crucial for effective SQL querying and data manipulation.