CODEWITHSUNDEEP
sql-servert-sqlgroup-bysql-server-2012
Serdia
Serdia

Reputation: 4428

How to group by with NULL values

What would be the simplest way to group by when NULL values?

declare @MyTable Table (ID int, Name varchar(50),Coverage varchar(50), Premium money)
insert into @MyTable values (1,'Robert', 'AutoBI', 100),
                            (1,'Robert', NULL, 300),
                            (2,'Neill','AutoBIPD',150),
                            (2,'Neill','AutoBI',200),
                            (3,'Kim', 'Collision',50),
                            (3,'Kim',NULL,100),
                            (4,'Rick','AutoBI',70),
                            (5,'Lukasz','Comprehensive',50),
                            (5,'Lukasz','NULL',25)

select ID, 
        Name, 
        Coverage, 
        sum(Premium) as Premium 
from @MyTable
group by ID
        ,Name   
        ,Premium
        ,Coverage

The outcome looks like this:

enter image description here

As you can see there is NULL value for name 'Robert'. How can I have summed premium ($400) and only one line without NULL Coverage?

But I need to make it look like this:

enter image description here

I cannot use MAX() function in this case.

Upvotes: 2

Views: 545

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

This solution assumes that NULL will be grouped to one "random" NOT NULL value within ID/Name. If more than single value is poissible then this query won't return stable result sets between executions:

select ID, 
        Name, 
        ISNULL(m1.Coverage, sub.Coverage) AS Coverage,
        sum(Premium) as Premium
FROM  @MyTable m1
cross apply (SELECT TOP 1 m2.Coverage FROM @MyTable m2 WHERE Coverage IS NOT NULL 
             AND m1.ID = m2.ID AND m1.Name = m2.Name) sub
group by ID
        ,Name   
        ,ISNULL(m1.Coverage, sub.Coverage);

Rextester Demo

Upvotes: 1

Related Questions