---
title: "What are the conditional aggregate functions in SQL, such as SUM(CASE...), and how are they used?"  
description: "What are the conditional aggregate functions in SQL, such as SUM(CASE...), and how are they used?"  
author: "Revati S Misra"  
published: 2023-07-04  
updated: 2023-07-05  
canonical: https://www.mindstick.com/forum/158949/what-are-the-conditional-aggregate-functions-in-sql-such-as-sum-case-and-how-are-they-used  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

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

What are the conditional [aggregate functions](https://www.mindstick.com/forum/159814/explain-the-differences-between-aggregate-functions-and-scalar-functions-in-sql) in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database), such as [SUM](https://www.mindstick.com/forum/159332/how-can-i-use-sum-in-linq)(CASE...), and how are they used?

## Replies

### Reply by Aryan Kumar

Conditional [aggregate](https://www.mindstick.com/blog/52/aggregate-functions-in-database) [functions in SQL](https://www.mindstick.com/forum/33707/what-is-the-difference-between-rank-and-dense_rank-functions-in-sql-server) 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

```plaintext
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.


---

Original Source: https://www.mindstick.com/forum/158949/what-are-the-conditional-aggregate-functions-in-sql-such-as-sum-case-and-how-are-they-used

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.