SQL Server group by absorb null and empty values

Question

I have this data:

Id  Name     amount    Comments 
-------------------------------
1     n1     421762    Hello    
2     n2        421    Bye      
3     n2        262    null     
4     n2       5127    ''

Each name may or may not have extra rows with null or empty comments.

How can I group by name and sum(amount) such that it ignores/absorbs the null or empty comments in the grouping and shows me only 2 groups.

Output I want:

Id   Name     sum(amount)   Comments 
------------------------------------
1     n1         421762     Hello    
2     n2           5180     Bye

I can't figure this out.

I hoped that would ignore the null/empty values but I always end up with 4 groups

select id, name, sum(amount), comments 
from table 
group by id, name, comments

You're grouping by comments too. If you *don't* want that, don't add `comments` in the `group by` clause — Panagiotis Kanavos, Mar 06 '19 at 16:24
Is there a reason you aren't just eliminating them? `where comments is not null and comments <> ''` — Jen R, Mar 06 '19 at 16:26
@heyNow *which* comments? All of them? Some of them? You can use `STRING_AGG` in SQL Server 2017 to aggregate (concatenate) strings just as you would use eg `SUM()` on a numeric field. Or you can use MIN/MAX to return the first/last comment — Panagiotis Kanavos, Mar 06 '19 at 16:27
@heyNow which ID do you want to display? Your desired output shows you want to retrieve the minimum ID per name. Is that what you want? — Panagiotis Kanavos, Mar 06 '19 at 16:28
Edited question. I'm using sql-server-2012. Each name may or may not have extra rows with null or empty comments. Id isn't really that important — heyNow, Mar 06 '19 at 16:31
@heyNow `Each name may or may not have extra rows` meaning? What happens if there are three different comments for a single name and one of them is a null? Do you want to display only *one* comment? All comments? — Panagiotis Kanavos, Mar 06 '19 at 16:40
sorry that part should have been clearer, there will only be either (1 comment and/or null/blank comment) per name, not multiple different non-null comments — heyNow, Mar 06 '19 at 16:46
@heyNow in which case a simple `MAX(comment), MIN(ID)` will be fine — Panagiotis Kanavos, Mar 06 '19 at 16:46

score 3 · Answer 1 · answered Mar 06 '19 at 16:24

3

Just use MAX() with comments:

select id, name, sum(amount), MAX(comments) 
from table 
group by id, name;

answered Mar 06 '19 at 16:24

Yogesh Sharma

49,870
5
26
52

Zaynul Abadin Tuhin · Answer 2 · 2019-03-06T17:16:13.697

1

use row_number()

  select id,name,comments,amount from 
     (select id,name,
     row_number() over(partition by Name  order by  case when Comments is not null then 1 else 2 end) rn,
       sum(amount) over(partition by Name) as amount,comments
   from table_name
      ) a where a.rn=1

edited Mar 06 '19 at 17:16

answered Mar 06 '19 at 16:32

Zaynul Abadin Tuhin

31,407
5
33
63

This *doesn't* produce the desired output. The amount should be summed – Panagiotis Kanavos Mar 06 '19 at 16:45

score 1 · Answer 3 · answered Mar 06 '19 at 16:35

Select Top 1 with Ties
       ID 
      ,Name
      ,Amount = sum(Amount) over (Partition By Name)
      ,Comments = max(Comments) over (Partition By Name)
from YourTable
Order by Row_Number() over (Partition By Name Order by ID)

Returns

ID  Name    Amount  Comments
1   n1      421762  Hello
2   n2      5810    Bye

score 1 · Accepted Answer · answered Mar 06 '19 at 16:44

You can't have a field in the SELECT statement unless it's part of the GROUP BY clause or used for aggregation. The question and desired output shows that the rows should be grouped by name, which means all other fields (ID,amount, comments) should be aggregated.

The question doesn't specify how the IDs should be aggregated, or which comments should appear. Aggregating strings is only possible using functions like MIN/MAX in all SQL Server versions up to 2016. SQL Server 2017 added STRING_AGG to concatenate strings. In earlier versions people have to use one of many string aggregation techniques that may involve XML or SQLCLR functions.

In SQL Server versions the desired output can be produced by

SELECT MIN(ID) as ID,name,sum(amount) as Amount, max(comment) as comments
from #table1 
group by name

This produces the desired output :

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

This assumes that there is only one non-empty comment. The question doesn't specify something different though.

In SQL Server 2017 multiple comments can be concatenated with STRING_AGG :

SELECT MIN(ID) as ID,name,sum(amount) as Amount, STRING_AGG(comment,' ') as comments
from table1 
group by name

Given the question's data, this will also produce the desired output.

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

SQL Server group by absorb null and empty values

4 Answers4

Linked