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

Hi,I have table like this.compId CompName CompValue1 x ?1 x ?1 x 11 x 2Now, I want the output like this.compID CompName Totalcount Nullcount %ofNotNull1 x 4 2 50%.I need to do this in sql assistant? Can someone help me?

Meem 24 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 PerctageNotNullFROM DB.TempTableGROUP BY 1,2;Thanks

Jimm 298 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);SelectcompID ,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 VT1Group 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.