Link to home
Start Free TrialLog in
Avatar of Russ Suter
Russ Suter

asked on

Conditional SUM Query

I have a SQL query similar to the following:

SELECT
 SUM(CASE WHEN SUBSTRING([CardholderAccountNumber], 1, 1) = '4' THEN [TransactionAmount] ELSE 0 END) AS [VisaVolume],
 SUM(CASE WHEN CAST(SUBSTRING([CardholderAccountNumber], 1, 2) AS int) BETWEEN 51 AND 55 THEN [TransactionAmount] ELSE 0 END) AS [MastercardVolume],
 SUM(CASE WHEN SUBSTRING([CardholderAccountNumber], 1, 4) = '6011' THEN [TransactionAmount] ELSE 0 END) AS [DiscoverVolume],
 SUM(CASE WHEN SUBSTRING([CardholderAccountNumber], 1, 2) = '34' OR SUBSTRING([CardholderAccountNumber], 1, 2) = '37' THEN [TransactionAmount] ELSE 0 END) AS [AmexVolume],
 SUM(CASE WHEN /* EVERYTHING ELSE */ THEN [TransactionAmount] ELSE 0 END) AS [OtherVolume],
 SUM([TransactionAmount]) AS [TotalVolume]
FROM
 [FinancialRecords]
WHERE
 [TransactionDate] = @Date

Open in new window


This works fine for most but the problem is with the /* EVERYTHING ELSE */ part. I really need this data returned as a single row but I can't figure out how to calculate the /* EVERYTHING ELSE */ part.

Can anyone offer advice? Is there a better way to craft this query?
Avatar of ezinder
ezinder

sounds like you just need to get the count(1) of "every record", then subtract the values of the other sum(case conditions) counts..

so..

select Amex, Discovery, ... Visa, TotalRecords-Amex-Discovery-Visa as AllOthers
from (
select
count(1) as TotalRecords,
sum(case when ... then 1 else 0 end) as Amex,
sum(case when ... then 1 else 0 end) as Discovery,
...
sum(....) as Visa
) as T1 (TotalRecords, Amex, Discovery, MasterCard, Visa)


Or, it may be that you actually want to do is use a UDF instead.  Put all your logic for all the inputs into a UDF and return 0 or 1 for each of the card types, including everything else..
then what you can do is sum those values..
just do the following:

SELECT
 SUM(CASE WHEN cardtype = 1 THEN [TransactionAmount] ELSE 0 END) AS [VisaVolume],
 SUM(CASE WHEN cardtype = 2 THEN [TransactionAmount] ELSE 0 END) AS [MastercardVolume],
 SUM(CASE WHEN cardtype = 3 THEN [TransactionAmount] ELSE 0 END) AS [DiscoverVolume],
 SUM(CASE WHEN cardtype = 4 THEN [TransactionAmount] ELSE 0 END) AS [AmexVolume],
 SUM(CASE WHEN cardtype = 5 THEN [TransactionAmount] ELSE 0 END) AS [OtherVolume],
 SUM([TransactionAmount]) AS [TotalVolume]
FROM (
	select  *,
		case 
			when SUBSTRING([CardholderAccountNumber], 1, 1) = '4' then 1
			when CAST(SUBSTRING([CardholderAccountNumber], 1, 2) AS int) between 51 and 55 then 2
			when SUBSTRING([CardholderAccountNumber], 1, 4) = '6011' then 3
			when SUBSTRING([CardholderAccountNumber], 1, 2) in ('34', '37') then 4
			else 5 
		end as cardtype
	from [FinancialRecords]
) a
WHERE [TransactionDate] = @Date
                                  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of awking00
SUM(CASE WHEN SUBSTRING([CardholderAccountNumber], 1, 1) = '4' THEN 0
     WHEN SUBSTRING([CardholderAccountNumber], 1, 2) in ('34','37','51','52','53','54','55') THEN 0
     WHEN SUBSTRING([CardholderAccountNumber], 1, 4) = '6011' THEN 0
     ELSE [TransactionAmount]
END) AS [OtherVolume]
SELECT
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '4%' THEN [TransactionAmount] ELSE 0 END) AS [VisaVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '5[12345]%' THEN [TransactionAmount] ELSE 0 END) AS [MastercardVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '6011%' THEN [TransactionAmount] ELSE 0 END) AS [DiscoverVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '3[47]%' THEN [TransactionAmount] ELSE 0 END) AS [AmexVolume],
 SUM(CASE WHEN NOT (
               [CardholderAccountNumber] LIKE '4%' OR
               [CardholderAccountNumber] LIKE '5[1-5]%' OR
               [CardholderAccountNumber] LIKE '6011%' OR
               [CardholderAccountNumber] LIKE '3[47]%' OR
               )
     THEN [TransactionAmount] ELSE 0 END) AS [OtherVolume],
 SUM([TransactionAmount]) AS [TotalVolume]
FROM
 [FinancialRecords]
WHERE
 [TransactionDate] = @Date


OR, for the "Other" part, derive it using an outer query:

SELECT
    [VisaVolume],
    [MastercardVolume],
    [DiscoverVolume],
    [AmexVolume],
    [TotalVolume] - [VisaVolume] - [MastercardVolume] - [DiscoverVolume] - [AmexVolume] AS OtherVolume,
    [TotalVolume]
FROM (
SELECT
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '4%' THEN [TransactionAmount] ELSE 0 END) AS [VisaVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '5[12345]%' THEN [TransactionAmount] ELSE 0 END) AS [MastercardVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '6011%' THEN [TransactionAmount] ELSE 0 END) AS [DiscoverVolume],
 SUM(CASE WHEN [CardholderAccountNumber] LIKE '3[47]%' THEN [TransactionAmount] ELSE 0 END) AS [AmexVolume],
 SUM([TransactionAmount]) AS [TotalVolume]
FROM
 [FinancialRecords]
WHERE
 [TransactionDate] = @Date
) AS derived