如何在不将NULL替换为0的情况下对含NULL的3列求和？

Got it, let's tackle this problem head-on. You need to sum three columns where some might hold NULL values, and you don't want to convert NULL to 0 because they carry distinct meanings (NULL = not started, 0 = not worked). Here's how to handle this properly without compromising your semantic requirements:

Solution 1: Row-wise Sum (Calculate Total per Row) #

If you're trying to compute the sum of the three columns for each individual row (ignoring NULLs in that row), use a subquery with the SUM() function. SUM() automatically skips NULL values, so it won't treat them as 0, and will only sum the non-NULL values in the row. If all three columns are NULL for a row, it returns NULL (which aligns with your "not started" semantic for the entire row).

SELECT 
    your_primary_key_column, -- Replace with your actual ID/key column
    (SELECT SUM(col) FROM (VALUES (col1), (col2), (col3)) AS temp_cols(col)) AS row_total
FROM your_table_name; -- Replace with your table name

Example Breakdown #

Suppose your table has this data:

The query will return:

• For row 1: col2 is NULL (not started), so we only sum 15 + 25 = 40
• For row 2: All columns are NULL, so the total remains NULL
• For row 3: col2 is 0 (not worked), so we include it in the sum (8 + 0 + 12 = 20)

Solution 2: Overall Table Sum (Total All Non-NULL Values Across Columns) #

If you want to calculate the grand total of all non-NULL values across the three columns for the entire table, just use SUM() on each column individually and add the results. This works because SUM() ignores NULLs within each column, and only sums the valid values.

SELECT 
    SUM(col1) + SUM(col2) + SUM(col3) AS overall_total
FROM your_table_name;

Note: If an entire column is NULL (all rows are "not started"), SUM(col) will return NULL for that column. Adding NULL to other sums will result in NULL. If you want to exclude fully NULL columns from the grand total (i.e., treat a fully NULL column as contributing nothing), you can use COALESCE() here—but only for the column-level sums, not individual NULL values. This still preserves your semantic distinction between NULL and 0:

SELECT 
    COALESCE(SUM(col1), 0) + COALESCE(SUM(col2), 0) + COALESCE(SUM(col3), 0) AS overall_total
FROM your_table_name;

Why Not Just col1 + col2 + col3? #

Directly adding columns will return NULL if any of the columns is NULL (since SQL treats any operation with NULL as NULL). For example, 15 + NULL + 25 would return NULL instead of 40, which isn't what you want. The subquery + SUM() approach avoids this issue entirely while respecting NULL's meaning.

内容的提问来源于stack exchange，提问作者Gauravsa