Skip to content

A very useful tip for summing NULLS in SELECT SQL Queries

December 3, 2015

On Tuesday, my colleague encountered an issued with trying to write an IF ELSE Statement to check for SQL declared values if they are NULL, and to replace this with 0. As SQL is not great at all at handling declared single record variables, and works only well for operational queries related to batched records. The best way to do this rather than declaring and looping values is to use this for summing columns that have NULL, and to replace this with 0.

So instead of writing this which if one of these columns contains NULL, the total will be NULL: –

SELECT 
    SUM(TotalHoursA + TotalHoursB + TotalHoursC) AS TOTAL
FROM SampleTable

The quick and easy solution to instantly fix this is to write: - 

IsNull(Column, 0)

SELECT
    SUM(IsNull(TotalHoursA, 0) + IsNull(TotalHoursB, 0) + IsNull(TotalHoursC, 0)) AS TOTAL
FROM Sample Table

Hope that helps a lot!!
Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: