Need help on syntax

Database
Enthusiast

Need help on syntax

Hi ,

I have data with null values and other values . I want to combine null values into Other and make it single row .

Please can any provide syntax for it.

 

 

Thanks.

1 REPLY
Highlighted
Senior Apprentice

Re: Need help on syntax

Hi,

 

We might need a bit more information about your data etc. but you might want to try using COALESCE as part of your processing. The following is an example.

create set volatile table  vt1
(col1 char(5)
,col2 integer)
on commit preserve rows;

insert into vt1 values('abc',1);
insert into vt1 values('abc',2);
insert into vt1 values(null,3);
insert into vt1 values('def',4);
insert into vt1 values(null,5);


select coalesce(col1,'Other') as ID_Col
  ,sum(col2)
from vt1
group by 1
order by 1;

This gives a result of:

id_col	Sum(col2)
abc	       3
def	       4
Other	8

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com