Q:

How to calculate percentage for sum of difference of two columns in sql

I have two columns in the same table, that needs to be summed and get the difference and then calculate the percentage?
SELECT sum([BillCount]) - sum([IssueCount]) as [Difference]
FROM Invoice
where [Year] = '2015'

In the above query I got the result for the difference as 244234. Now I need to calculate the percentage by dividing the difference with the actual Billcount 260918.
sample Data:
SELECT sum(BillCount) as bills, sum(IssueCount) as issues
FROM Invoice
where [Year] = '2015'

Result:
    Bills: 260918
    Issues: 16684
    Difference: 244234
    Divide Difference By actual Bills: 244234/260918 = 0.93
    Percentage: 0.93*100 = 93.60
I have used the changed divisor from zero to one. But I am getting the percentage result in many rows, like for each individual billcount. Is there any way that I could get the exact percentage in one row like we calculate in calculator?
Thanks.

A:

Like this?
SELECT sum([BillCount]) - sum([IssueCount]) as [Difference],
    (sum(IssueCount) - sum(BillCount)) / nullif(sum(BillCount), 0) as ptcdiff
FROM Invoice
where [Year] = '2015'

You'll note I changed the order of subtraction. This makes it so that its a negative change rather than a positive change. If you need an absolute. change, you can just throw abs() around the result. You can also bake this into a function if it makes sense to
declare 
    @pctOld float = 260918,
    @pctNew float = 16684  

select PctDiff = case when @PctNew is null or @PctOld is null then null
                       when @PctNew = null or @PctOld = null then null
                       when @PctNew = 0 and @PctOld = 0 then 0    
                       when @PctNew = 0 and @PctOld > 0 then -100.00
                       when @PctNew >= 0 and @PctOld < 0 then null
                       when @PctNew < 0 and @PctOld > 0 then null
                       when @PctOld = 0 then null
                       else ((@PctNew - @PctOld) / abs(@PctOld)) * 100.0
                  end

