Count&Sub Count

Database
Enthusiast

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?
3 REPLIES
Enthusiast

Re: Count&Sub Count

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
Enthusiast

Re: Count&Sub Count

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
Enthusiast

Re: Count&Sub Count

Thanks guys. Worked Great.