What are the conditional aggregate functions in SQL, such as SUM(CASE...), and how are they used?

Question

Aryan Kumar · Answer

Conditional aggregate functions in SQL are aggregate functions that allow you to apply a condition to the results of the aggregate function. This can be useful for calculating different values for different groups of data, or for calculating values based on the results of a logical expression.

The most common conditional aggregate function in SQL is SUM(CASE WHEN...), which allows you to calculate the sum of a column based on a condition. For example, the following query will calculate the sum of the salaries for all employees who are male:

SQL

SELECT SUM(CASE WHEN gender = 'Male' THEN salary ELSE 0 END) AS male_salary
FROM employees;

The CASE WHEN... statement in the SUM() function allows you to specify a condition that will be evaluated for each row in the employees table. If the condition is true, then the value of the salary column will be added to the sum. If the condition is false, then the value of 0 will be added to the sum.

Other conditional aggregate functions in SQL include:

AVG(CASE WHEN...): Calculates the average of a column based on a condition.
COUNT(CASE WHEN...): Calculates the number of rows in a table based on a condition.
MAX(CASE WHEN...): Calculates the maximum value of a column based on a condition.
MIN(CASE WHEN...): Calculates the minimum value of a column based on a condition.

Conditional aggregate functions can be a powerful tool for analyzing data in SQL. They allow you to calculate different values for different groups of data, or to calculate values based on the results of a logical expression.

Here are some examples of how conditional aggregate functions can be used:

To calculate the average salary for male and female employees.
To calculate the number of employees who are over 18 years old.
To calculate the maximum salary for employees who are in a certain department.
To calculate the minimum salary for employees who have been with the company for a certain number of years.

Conditional aggregate functions can be used in a variety of ways to analyze data in SQL. They are a powerful tool that can be used to answer a variety of questions about your data.

forum

What are the conditional aggregate functions in SQL, such as SUM(CASE...), and how are they used?

Can you answer this question?

1 Answers