CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Michael
Michael

Reputation: 431

How to aggregate either unique string value or NULL

Is there a way to aggregate a string value if it is unique or otherwise return NULL. For example, given the table "STUFF":

Col_A | Col_B | Col_C | Col_D
    1 | Foo   | Bar   |     6
    2 | Foo   | NoBar |     0
    2 | Foo   | Foo   |     4
    1 | Foo   | Bar   |     6

So that this SQL

SELECT SUM(COL_A), STR_AGG_FUNC(COL_B), STR_AGG_FUNC(COL_C),SUM(COL_D) FROM STUFF

Would return:

Col_A | Col_B | Col_C | Col_D
    6 | Foo   | NULL  |    16

Upvotes: 2

Views: 785

Answers (1)

S3S
S3S

Reputation: 25152

You can do this with max and a case statement on those columns.

select
   Col_A = sum(Col_A)
   ,Col_B = case when max(Col_B) = min(Col_B) then max(Col_B) else null end
   ,Col_C = case when max(Col_C) = min(Col_C) then max(Col_C) else null end
   ,Col_D = sum(Col_D)
From
   Stuff

Upvotes: 7

Related Questions