0

Is it safe to use SUM(NULLIF(V1=V2))? My aim is to know if V1 is equal to V2(both nullable) for all rows and since i am using this in a group statement this is what i come up with. Is there any other way to achieve the same?

Broccoli
  • 1
  • 1
    [NULLIF](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql) may also return Null. Thus it's possible `SUM` might break down or give improper results – boop_the_snoot Sep 02 '17 at 17:05
  • 2
    @Nobody I think SUM ignores null values. – Broccoli Sep 02 '17 at 17:11
  • I didn't knew that `SUM` ignores `NULL`. I always used `COALESCE` before doing `SUM`. But just have a look at [this post](https://stackoverflow.com/questions/37295336/is-it-safe-to-use-sum-without-isnull). Also thanks for the info :) – boop_the_snoot Sep 02 '17 at 17:28
  • 2
    Both are the same value or both are NULL: `sum(case when V1=V2 or (V1 is null and V2 is null) then 1 else 0 end)`? – dnoeth Sep 02 '17 at 17:42
  • can you tag what rdbms are you using? is it mysql? – Ferdinand Gaspar Sep 02 '17 at 22:02

2 Answers2

0

Its better to use COALESCE method before the SUM aggregation.

nitin.sharma0180
  • 471
  • 3
  • 6
  • 1
    No, it's not, because you add an unneccessary function. If you care about a possible final NULL it's better to use `COALESCE(SUM(col))` instead of `SUM(COALESCE(col,0))`, the former executes COALESCE once, the latter once per row. – dnoeth Sep 02 '17 at 18:30
0

To determine if they are equal for all rows, you can do:

select (1 -
        max(case when v1 = v2 then 0
                 when v1 is null and v2 is null then 0
                 else 1
             end)
        ) as all_equal
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786