Handling NULLs in SSRS Aggregates

By Tom Nonmacher

Aggregating data is a common operation in SQL Server Reporting Services (SSRS), but handling NULL values in these aggregations can often be a challenge. NULLs can sometimes skew results or create inconsistencies, especially when working with SUM and AVG functions. Understanding how different database technologies handle NULLs in aggregation functions is essential to ensuring accurate results. In this blog post, we will explore how SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse handle NULLs in the context of SSRS aggregates.

Starting with SQL Server 2019, the behavior of aggregate functions is such that NULLs are ignored during calculations. If you're running a SUM function and one of the values is NULL, SQL Server simply disregards it. However, if you need to consider NULLs as zeroes during your calculation, you can use the ISNULL function. Here's an example: 


-- SQL Server 2019 code
SELECT SUM(ISNULL(column_name, 0)) as Total
FROM table_name

MySQL 8.0, like SQL Server, also disregards NULLs during aggregations. However, the function to replace NULLs with a specific value in MySQL is COALESCE, not ISNULL. The usage is slightly different, but the result is the same. Here is an example: 


-- MySQL 8.0 code
SELECT SUM(COALESCE(column_name, 0)) as Total
FROM table_name

DB2 11.5 has a similar approach to handling NULLs in aggregates. It ignores NULLs during calculations, and we can use the COALESCE function to replace them. Here is an example: 


-- DB2 11.5 code
SELECT SUM(COALESCE(column_name, 0)) as Total
FROM table_name

Azure SQL and Azure Synapse, being part of the Microsoft family, follow the same rules as SQL Server 2019. They ignore NULLs in aggregates, and you can use the ISNULL function to replace NULLs with a specific value. Here's an example: 


-- Azure SQL and Azure Synapse code
SELECT SUM(ISNULL(column_name, 0)) as Total
FROM table_name

In conclusion, when working with SSRS aggregates, it's important to understand how your database technology handles NULLs to ensure accurate calculations. Whether you're using SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, or Azure Synapse, understanding the way they handle NULLs in aggregate functions can help you avoid unexpected results and maintain the integrity of your data. Always remember to consider whether you need to treat NULLs as zeroes or simply ignore them during your calculations.

Check out the latest articles from all our sites:

Privacy Policy for sqlsupport.org

Last updated: Feb 03, 2026

sqlsupport.org respects your privacy and is committed to protecting any personal information you may provide while using this website.

This Privacy Policy document outlines the types of information that are collected and recorded by sqlsupport.org and how we use it.

Information We Collect

  • Internet Protocol (IP) addresses
  • Browser type and version
  • Pages visited
  • Time and date of visits
  • Referring URLs
  • Device type

Cookies and Web Beacons

sqlsupport.org uses cookies to store information about visitors preferences and to optimize the users experience.

How We Use Your Information

  • Operate and maintain our website
  • Improve user experience
  • Analyze traffic patterns
  • Prevent fraudulent activity

Contact

Email: admin@sqlsupport.org




AD3E55
Please enter the code from the image above in the box below.