2
\$\begingroup\$

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

enter image description here

\$\endgroup\$
  • \$\begingroup\$ Is this for sql-server? Please tag accordingly. \$\endgroup\$ – 200_success Mar 2 '16 at 4:17
2
\$\begingroup\$

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...

\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ The trickery doesn't work as you GROUP BY o.PaymentType :-) \$\endgroup\$ – dnoeth Mar 2 '16 at 7:19
  • \$\begingroup\$ @dnoeth Why do you mean that? I've done similar stuff before? \$\endgroup\$ – holroy Mar 2 '16 at 10:10
  • \$\begingroup\$ Maybe similar, but not like this :-) You group all rows with the same PaymentType resulting in 5 groups: 'GO CARD', 'GOCARD', 'FOOD CARD', 'FUEL CARD', 'INVOICE PAYMENT'. Trying to get the MIN for a group where all rows share the same value is useless. \$\endgroup\$ – dnoeth Mar 2 '16 at 10:21
  • \$\begingroup\$ @dnoeth Normally I would agree with you but a little uncertain related when the upper enters the picture. \$\endgroup\$ – holroy Mar 2 '16 at 10:24
  • 2
    \$\begingroup\$ If you got a case sensitive collation (you need UPPERto get different upper/lower case variations) this column will also be case sensitive while grouping, adding new 'Go Card', 'go card', etc. groups. If it's not casespecific it will return one of the variations (probably random) and then MIN/MAX might be handy. Or you group by UPPER(o.PaymentType) \$\endgroup\$ – dnoeth Mar 2 '16 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.