How to add multiple columns in current select statement as a new field?

I have the following query:

select

    sum(case when c.BILLINGCLASSIFICATION = 'ACT Payment' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) AS [FTI-ACT Payment],
    sum(case when c.BILLINGCLASSIFICATION = 'Bulk' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-Bulk],
    sum(case when c.BILLINGCLASSIFICATION = 'Dedicated' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-DED],
    sum(case when c.BILLINGCLASSIFICATION = 'FMS' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-FMS],
    sum(case when c.BILLINGCLASSIFICATION =  'Managed Service' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-MS],
    sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Bulk','Dedicated','FMS','Managed Service') AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-Other],
    sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Corrected') and c.Balance>0 AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-DR],
    sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Cancelling') and c.Balance < 0 AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-CR],
    sum(case when c.VOUCHER like 'ARP%' and c.LedgerJournalACType = 'Bank' and c.TransType2 = 'Payment' THEN convert(float,c.Balance) ELSE 0 END) As [New Payments]
    
    
    

from [AX2cTest].[dbo].[CUSTTRANS_V] c

with this output:

 FTI-ACT Payment    FTI-Bulk    FTI-DED     FTI-FMS    FTI-MS   FTI-Other   FTI-DR      FTI-CR  New Payments
    -122995.14      114521.67   728830.9    2793.46    53137.07 3000       902352.58    -69.48  0

I need to add a balance field that simply adds all of these fields. This is just a sample of a rather long query though. I’m trying to avoid adding each lengthy statement into a (col1 + col2) format, This would seem to slow down a query and this will be querying a production environment soon. Is this my only option? thanks

Answer

The simplest option is a subquery:

select
    t.*,
    [FTI-ACT Payment] + [FTI-Bulk] + [FTI-DED] + [FTI-FMS] + [FTI-MS] + [FTI-Other] + [FTI-DR] + [FTI-CR] + [New Payments] AS total
from (
    select
        sum(case when c.BILLINGCLASSIFICATION = 'ACT Payment' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) AS [FTI-ACT Payment],
        sum(case when c.BILLINGCLASSIFICATION = 'Bulk' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-Bulk],
        sum(case when c.BILLINGCLASSIFICATION = 'Dedicated' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-DED],
        sum(case when c.BILLINGCLASSIFICATION = 'FMS' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-FMS],
        sum(case when c.BILLINGCLASSIFICATION =  'Managed Service' AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-MS],
        sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Bulk','Dedicated','FMS','Managed Service') AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-Other],
        sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Corrected') and c.Balance>0 AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-DR],
        sum(case when c.BILLINGCLASSIFICATION not in ('ACT Payment','Cancelling') and c.Balance < 0 AND c.TransType2 = 'Customer' THEN convert(float,c.Balance) ELSE 0 END) As [FTI-CR],
        sum(case when c.VOUCHER like 'ARP%' and c.LedgerJournalACType = 'Bank' and c.TransType2 = 'Payment' THEN convert(float,c.Balance) ELSE 0 END) As [New Payments]
    from [AX2cTest].[dbo].[CUSTTRANS_V] c
) t

As the question and query stand, there is not way you can avoid enumerating the columns you want to sum().

It might be possible, however, to build an additional sum() expression that brings together with or all individual conditions in the existing sum()s. This would look like:

sum(case 
    when 
        (c.BILLINGCLASSIFICATION = 'ACT Payment' AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION = 'Bulk' AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION = 'Dedicated' AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION = 'FMS' AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION =  'Managed Service' AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION not in ('ACT Payment','Bulk','Dedicated','FMS','Managed Service') AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION not in ('ACT Payment','Corrected') and c.Balance > 0 AND c.TransType2 = 'Customer')
        or (c.BILLINGCLASSIFICATION not in ('ACT Payment','Cancelling') and c.Balance < 0 AND c.TransType2 = 'Customer')
        or ( c.VOUCHER like 'ARP%' and c.LedgerJournalACType = 'Bank' and c.TransType2 = 'Payment')
    then convert(float,c.Balance) 
    else 0 
end) as total

We could work and try to shorten this by factorizing some of the conditions. Depending on your actual data, neater options may be available to shorten the conditions.