Please refer table below: enter image description here

I have column ID and Value in my table and ID can be duplicate. I would like to sum value column only if ID column is not null. If ID is Null, dont sum and show as it is.

I know I can do Union like below:

select ID, sum(Value)
from table where id is not null
group by ID
UNION
select ID, Value
from table where id is null

But I would like to achieve the same result using IIF or CASE or any function without UNION something like:

iif(ID is not null,sum([Value]),[Value]) as Value 

but this is not working.

CodePudding user response：

You can use the SUM() function in SQL with a CASE statement to achieve this. The basic syntax would look something like this:

SELECT SUM(CASE WHEN ID IS NOT NULL 
THEN Value ELSE 0 END) AS SumValue
FROM your_table

This query will sum the Value column only if the ID column is not null. If the ID column is null, the ELSE 0 statement will make sure that the value is not included in the sum.

You could also use a WHERE clause to only select the rows where ID is not null:

SELECT SUM(Value) AS SumValue
FROM your_table
WHERE ID IS NOT NULL

Hope this might help you.

CodePudding user response：

You might try out grouping sets to see if that produces a better plan. This does assume that values (column 2) in the null subset are themselves unique:

select id, sum(val) from T
group by grouping sets ((id), (id, val))
having id is null and val is not null or id is not null and val is null;

And then there's always an approach that generates a new id for the null id so they can remain distinct during grouping. Here I'm assuming that negative values are open for this purpose but you could probably adjust if necessary:

with data as (
    select coalesce(id, -row_number() over (order by id)) as id, val
    from T
)
select case when id >= 0 then id end as id, sum(val)
from data group by id;

https://dbfiddle.uk/fbGbpyEf