Storing transactions with amounts and payment types

Question

I have a table where I'm storing a row for each transaction that records the type of transaction along with the amount of money being recorded for the transaction type. I then need to write a report to sum the amounts of various types.

At the moment I'm doing this with a series of CTE's, but I'm sure there's a better way:

;with cte_foodCard as (
    select  isnull(sum(cast(o.FinancialContribution as money)), 0) as 'FoodCardContributions'
    from    dbo.CriticalAssistanceInternalOutcomes as o
    where   upper(o.PaymentType) = 'FOOD CARD'
), cte_fuelCard as
(
    select  isnull(sum(cast(o.FinancialContribution as money)), 0) as 'FuelCardContributions'
    from    dbo.CriticalAssistanceInternalOutcomes as o
    where   upper(o.PaymentType) = 'FUEL CARD'
), cte_gocard as
(
    select  isnull(sum(cast(o.FinancialContribution as money)), 0) as 'GoCardContributions'
    from    dbo.CriticalAssistanceInternalOutcomes as o
    where   upper(o.PaymentType) in ( 'GO CARD' , 'GOCARD')
), cte_invoicePayment as
(
    select  isnull(sum(cast(o.FinancialContribution as money)), 0) as 'InvoicePaymentContributions'
    from    dbo.CriticalAssistanceInternalOutcomes as o
    where   upper(o.PaymentType) = 'INVOICE PAYMENT'
)
select *
from cte_foodCard , cte_fuelCard, cte_gocard, cte_invoicePayment

For more context, a sampling of the data from development (needs better rules and cleanup) looks like:

select  o.Id ,
        o.FinancialContribution ,
        o.PaymentType
from dbo.CriticalAssistanceInternalOutcomes as o

Answer 1

When you use the same base query with different WHERE-conditons you can usually rewrite it using Conditional Aggregation with CASE (aka pivoting):

select -- you can easily add a GROUP BY column here
   sum(cast(case when upper(o.PaymentType) = 'FOOD CARD' 
            then o.FinancialContribution end else 0 as decimal(18,4))) as FoodCardContributions
  ,sum(cast(case when upper(o.PaymentType) = 'FUEL CARD' 
            then o.FinancialContribution end else 0 as decimal(18,4))) as FuelCardContributions
  ,sum(cast(case when upper(o.PaymentType) in ('GO CARD' , 'GOCARD')
            then o.FinancialContribution end else 0 as decimal(18,4))) as GoCardContributions
  ,sum(cast(case when upper(o.PaymentType) = 'INVOICE PAYMENT' 
            then o.FinancialContribution end else 0 as decimal(18,4))) as InvoicePaymentContributions
from dbo.CriticalAssistanceInternalOutcomes as o
where
   upper(o.paymentType)
     in ('GO CARD', 'GOCARD', 'FOOD CARD', 'FUEL CARD', 'INVOICE PAYMENT')

I changed the deprecated MONEY to a better DECIMAL.

Btw, who created that table? Storing numeric values in a VarChar column is very bad practice...

Answer 2

I might be missing something because I don't really understand why you have the four almost identical statements at the start, which only vary in the name and in the o.PaymentType. My suggestion would be something along the following lines:

select  sum(o.FinancialContribution),
        min(o.PaymentType)
from dbo.CriticalAssistanceInternalOutcomes as o
where upper(o.paymentType) 
   in ('GO CARD', 'GOCARD', 'FOOD CARD', 'FUEL CARD', 'INVOICE PAYMENT')
group by o.PaymentType

Notice the trickery using Min(o.PaymentType) to make that a groupable version of something which might vary just a little. Due to the same reason I also removed the o.Id from the column list.

Given that I haven't made any stupid typos, this SQL should provide you the sums you are asking for in a simpler statement.