Storing transactions with amounts and payment types 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
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...