Sql: sum 3 columns when one column has a null value?

sql

null-handling

sql-queries

database-optimization

byAnton Shumikhin·Dec 29, 2024

Wave goodbye to NULL issues! Utilize COALESCE to seamlessly sum three columns even with NULL values:

-- SQL: Making NULL values less intimidating since 1972
SELECT column1 + COALESCE(column2, 0) + COALESCE(column3, 0) AS total
FROM your_table;

COALESCE turns NULL to 0, making your sums accurate and reliable.

Tackling NULLs in SQL

In most SQL summations, NULL values tend to be silent math disrupters that could skew your results. Luckily, we have a few tricks up our sleeves.

NULL handling in different SQL dialects

SQL speaks multiple dialects. Your database's specific syntax dictates how NULL values should be handled:

SQL Server: embrace the ISNULL function:

-- ISNULL: because NULL has feelings too
SELECT ISNULL(column1, 0) + ISNULL(column2, 0) + ISNULL(column3, 0) AS total
FROM your_table;

MySQL and SQLite: trust in the power of IFNULL:

-- IFNULL: making NULL less null and void
SELECT IFNULL(column1, 0) + IFNULL(column2, 0) + IFNULL(column3, 0) AS total
FROM your_table;

Oracle: NVL got your back:

-- NVL: because sometimes, we all need a little validation
SELECT NVL(column1, 0) + NVL(column2, 0) + NVL(column3, 0) AS total
FROM your_table;

PostgreSQL: follows the SQL standard and uses COALESCE, just like SQL Server.

Advanced SQL: more than basic sums

In complex queries, with aggregations, GROUP BY clauses, or conditional summation based on other column values, you may need the power of CASE statements or conditional aggregates:

-- SQL: helping you CASE the joint since '72
SELECT 
    SUM(CASE WHEN column1 IS NULL THEN 0 ELSE column1 END) 
    + SUM(CASE WHEN column2 IS NULL THEN 0 ELSE column2 END) 
    + SUM(CASE WHEN column3 IS NULL THEN 0 ELSE column3 END) AS total
FROM your_table
GROUP BY your_grouping_column;

This grants more granular control for conditional logic.

COALESCE vs ISNULL

While COALESCE and ISNULL appear functionally similar, COALESCE boasts versatility and is more portable across different SQL databases. Here's how it works:

-- COALESCE: the lottery you're always bound to win!
SELECT COALESCE(val1, val2, val3, ..., valN) FROM your_table;

Watch out for pitfalls!

Beware of the type inference caveat. COALESCE and ISNULL may infer the resulting data type differently in some cases. Be sure to check your DB's specifics to prevent unpleasant surprises.

Efficient and accurate query structure

Simplicity is key to efficient queries, sometimes replacing COALESCE with a simple + improves performance:

-- SQL: where + isn't just a mathematical operator, it's a way of life!
SELECT (column1 + column2 + column3) AS total
FROM your_table
WHERE column1 IS NOT NULL AND column2 IS NOT NULL AND column3 IS NOT NULL;