All Forums Database
Rajasan77 10 posts Joined 07/09
02 Sep 2009
Count&Sub Count

Hi,

I have table like this.

compId CompName CompValue
1 x ?
1 x ?
1 x 1
1 x 2

Now, I want the output like this.

compID CompName Totalcount Nullcount %ofNotNull
1 x 4 2 50%.

I need to do this in sql assistant? Can someone help me?

Meem 22 posts Joined 05/07
03 Sep 2009

Try something like this...

SELECT
CompId,
CompName,
COUNT(COALESCE(CompValue,1)) TotalCount,
TotalCount - COUNT(CompValue) NullCount,
((TotalCount - NullCount) (Decimal(5,2))) /TotalCount *100 PerctageNotNull
FROM
DB.TempTable
GROUP BY 1,2;

Thanks

Jimm 165 posts Joined 09/07
03 Sep 2009

Count does not count the number of mulls.

Create volatile table vt1
(CompID smallint not null
, compName Char
, compValue smallint)
primary index (CompID)
on commit preserve rows ;

insert into vt1 values (1,'A',1);
insert into vt1 values (1,'A',null);
insert into vt1 values (1,'A',2);

Select
compID ,
compName,
Count(Zeroifnull(CompValue)) as TotalCount,
TotalCount - Count(CompValue) as NullCount,
Cast ((((TotalCount - NullCount) * 1.00 / TotalCount *100 ) (Format '99%')) AS Char(3)) As "%NotNull"
From VT1
Group By 1,2
;

HTH

Rajasan77 10 posts Joined 07/09
03 Sep 2009

Thanks guys. Worked Great.

You must sign in to leave a comment.