SQL Server SUM IF

Summary: in this tutorial, you will learn about the SQL Server SUM IF function to calculate the sum of values based on a condition.

Introduction to SQL Server SUM IF #

In SQL Server, the SUM is an aggregate function that allows you to calculate the total of values in a set. Here’s the syntax of the SUM function:

SUM(expresion)Code language: SQL (Structured Query Language) (sql)

The IIF function allows you to return a value when a condition is true or another value when the condition is false. The following shows the syntax for the IIF function:

IIF(condition, value_if_true, value_if_false)Code language: SQL (Structured Query Language) (sql)

When combining the SUM function with the IIF function, you can calculate the sum of values based on a condition.

The following shows how to use the SUM function with the IIF function:

SUM(IIF(condition, value_to_sum_when_true, value_to_sum_when_false))Code language: SQL (Structured Query Language) (sql)

In this expression:

• condition: Specify the condition that you want to include values for calculating the total.
• value_to_sum_when_true: Specify the value that you want to calculate the total if the condition is true.
• value_to_sum_when_false: Specify the value that you want to calculate the total when the condition is false.

SQL Server SUM IF example #

Let’s explore an example of using the SUM IF.

We’ll use the sales.orders and sales.order_items tables from the sample database:

1) Basic SUM IF example #

The following example uses the SUM function with the IIF function to calculate the total amount of pending orders with the order status 1:

SELECT
  SUM(
    IIF(
      o.order_status = 1, 
      quantity * list_price * (1 - discount), 
      0
    )
  ) total_pending_amount 
FROM
  sales.order_items i 
  INNER JOIN sales.orders o ON o.order_id = i.order_id;Code language: SQL (Structured Query Language) (sql)

Output:

total_pending_amount
--------------------
388739.5422Code language: plaintext (plaintext)

The output indicates that the total pending amount is about 388,739.

2) Using multiple SUM IF example #

The following example uses the SUM with the IIF function to calculate the total amount of pending and rejected orders with the order status 1 and 3 respectively:

SELECT
  SUM(
    IIF(
      o.order_status = 1, 
      quantity * list_price * (1 - discount), 
      0
    )
  ) total_pending_amount,
  SUM(
    IIF(
      o.order_status = 3, 
      quantity * list_price * (1 - discount), 
      0
    )
  ) total_rejected_amount 
FROM
  sales.order_items i 
  INNER JOIN sales.orders o ON o.order_id = i.order_id;Code language: SQL (Structured Query Language) (sql)

Output:

total_pending_amount | total_rejected_amount
---------------------+----------------------
388739.5422          | 208579.4531Code language: plaintext (plaintext)

Summary #

• Use the SUM function with the IIF function to form a SUM IF function that returns the total values based on a condition.