Divison and subquery in select

I want to divide the total of accounts from ‘ed’ table against the ‘e’ table and I am getting a zero, Table ‘ed’ has 16784 accounts and ‘e’ table has 41601 accounts so I want to divide 16784/41601 but I am getting a zero in the ouput not sure what I am doing wrong.

SELECT count(distinct ed.accountnumber)/(select count(distinct e.accountnumber) as totalaccts from revenueanalytics.dbo.EPICDATA e) as totalaccounts

                from revenueanalytics.dbo.EPICDATA ed

                where ed.NEW_AGING_GROUP in ('91-120','121-180','181-365','366+')

Answer

You are facing integer division: both operands are integer, so the database does the computation in integer context (and hence returns an integer value).

To avoid that, you can simply multiply one of the values with a decimal number, so this forces decimal context in the computation.

Also, you can avoid the need for a subquery by using conditional aggregation: this works by moving the filtering within the aggregate function, like so:

select 
    1.0 
        * count(distinct 
            case when new_aging_group in ('91-120','121-180','181-365','366+') 
            then ed.accountnumber 
        end)
        / count(distinct e.accountnumber)
        as totalaccounts
from revenueanalytics.dbo.epicdata