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
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

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?