CODEWITHSUNDEEP
sqlsql-servergroup-by
Andreas
Andreas

Reputation: 392

SQL GROUP BY with null values

i have these data in an MSSQL 2012r2 table: 

id   data1  amount
------------------
10   abc     95.00
10   NULL   312.00
20   def     16.00
30   gqi     32.00

Expected query result:

id   data1  amount
------------------
10   abc    407.00
20   def     16.00
30   gqi     32.00

is this possible using only sql?

Upvotes: 0

Views: 1333

Answers (6)

Ravi Sharma
Ravi Sharma

Reputation: 370

select id, max(data1) data1,sum(amount) amount from tablename group by id

Upvotes: 0

Jeet Kumar
Jeet Kumar

Reputation: 547

Please try this.

select t1.id, coalesce(t1.data1,t2.data1)
from Table t1 join (select id, sum(amount) from Table group by id) t2 
on t1.id = t2.id

here, major problem is only how to get not null value among multiple columns. So we can achieve it by coalesce function.

What coalesce function do?

The COALESCE() function returns the first non-null value in a list.

Example:

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

After executing above sql query it returns.

W3Schools.com

which is first not null value.

https://www.w3schools.com/sql/func_sqlserver_coalesce.asp

Upvotes: 0

Talha
Talha

Reputation: 85

select id, COALESCE(data1 ,'anyvalue') as data1, sum(amount) as amount 
from <table>
group by id , COALESCE(data1,'anyvalue')

Upvotes: -1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

case when can also help you

select id, case when data1 is null then 'abc' else data1 end
          as data1, sum(amount) from table_name
         group by id,case when data1 is null then 'abc' else data1 end

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you want NULL to look like 'abc', then use coalesce():

select id, coalesce(data1, 'abc') as data1,
       sum(amount) as amount
from t
group by id, coalesce(data1, 'abc');

This will create an arbitrary 'abc' row if it doesn't exist. If you just want it merged with any existing value:

select t.id, coalesce(t.data1, t2.data1) as data1,
       sum(t.amount) as amount
from t outer apply
     (select top (1) t2.data1
      from t t2
      where t2.id = t.id and t2.data1 is  not null
     ) t2
group by t.id, coalesce(t.data1, t2.data1);

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use max(),sum() aggregated function and group by

select id, max(data1),sum(amount)
from tablename
group by id

Upvotes: 2

Related Questions