Percentage calculation always returns me 0 even that the values are numbers

Question

I'm trying to create a percentage for some errors in my table. I build a query that brings the qty of errors per each value, the total errors, and the division. But is always giving me 0 (I added a check in order to see if the values are numeric) .

select 
ZZ0010 as Error_type
, qty
, total
,running_sum
, isnumeric(total)
,isnumeric(running_sum)
,running_sum/total

from (
    select ZZ0010
            ,(count(  [ZZ0010] )) as qty
             ,sum(nullif(count(  [ZZ0010] ),0) ) over(order by count(  [ZZ0010] )  desc,ZZ0010) as running_sum 
             ,sum(nullif(count(  [ZZ0010] ),0) ) over() as total
    from md.CAR_CRM_DS_ZCRM_Z101_BUSINESS_ATTR_VT   
    group by ZZ0010
    having (count(  [ZZ0010] )) is not null 
                                                ) tbl
order by running_sum asc

Error_type	qty	total	running_sum	isnumeric(total)	isnumeric(running_sum)	running_sum/total
2	2123	3931	2123	1	1	0
10	1808	3931	3931	1	1	0

Answer 1

Hmmm . . . I think you can radically simplify this:

select ZZ0010,
       count(*) as qty,
       sum(count(*)) over (order by count(*) desc) as running_sum,
       sum(count(*)) over () as total,
       ( sum(count(*)) over (order by count(*) desc) * 1.0 /
         nullif(sum(count(*)) over (), 0)
       ) as ratio
from md.CAR_CRM_DS_ZCRM_Z101_BUSINESS_ATTR_VT   
group by ZZ0010;

Notes:

• I don't know why you would use isnumeric() on number columns.
• COUNT() cannot return NULL so the HAVING is redundant.
• Use NULLIF() to avoid division by 0. Of course, the sum of counts cannot be zero in your query unless all rows have ZZ0010 as NULL.
• SQL Server does integer division. I just multiply by 1.0 to avoid this.
• NULLIF(COUNT(), 0) is just really strange. Why distinguish between 0 and NULL in a column that ignores nulls?
• I don't think a subquery is particularly useful in this case, but if you don't want to repeat the expressions, you can certainly use a subquery.