CODEWITHSUNDEEP
sqlgroup-bycountsubquerydivision
cardonas
cardonas

Reputation: 109

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+')

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want the ratio of these numbers in the same table, then the simplest way is to use avg() with conditional logic:

select avg(case when ed.NEW_AGING_GROUP in ('91-120', '121-180', '181-365', '366+')
                then 1.0 else 0
           end) as ratio
from revenueanalytics.dbo.EPICDATA ed;

This assumes that you don't really need the count(distinct).

Upvotes: 0

GMB
GMB

Reputation: 222402

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

Upvotes: 1

Related Questions