Total records from a Case when substring statement

Analytics

Total records from a Case when substring statement

Hi,

Im attempting to create a TOTAL colums for the number of returns from each case statement for the below, but it keeps totalling all columns in the table opposed to just my case when statements, any ideas how to resolve this?

Select

TRANSACTION_DT AS "Transaction Date",

SUM(CASE WHEN SUBSTRING(COL 1 FROM 9 FOR 1) IN ('1','3','5','7','9','B','D','F') THEN 1 else 0 end) AS RULE 1,

SUM(CASE WHEN SUBSTRING(COL 2 FROM 3 FOR 1) IN ('2','3','6','7','A','B','E','F') THEN 1 else 0 end) AS RULE 2,

SUM(CASE WHEN SUBSTRING(COL 3FROM 2 FOR 1) IN ('2','3','6','7','A','B','E','F') THEN 1 ELSE 0 END) AS RULE 3,

SUM(CASE WHEN SUBSTRING(COL 4 FROM 16 FOR 1) IN ('1','3','5','7','9','B','D','F') THEN 1 ELSE 0 END ) AS RULE 5,

COUNT(COL 1,COL 2,COL 3,COL 4) as TOTAL,

COUNT(DISTINCT COL 1,COL 2,COL 3,COL 4) AS "UNIQUE CASES PER DAY"

 

FROM DQVIEWSP_VERDE.CC_FALCON_CREDIT_AUTH_TXN

Where transaction_dt >= '2014-01-01'

1 REPLY
Junior Contributor

Re: Total records from a Case when substring statement

This query will return a syntax error as you can't COUNT(multiple columns).

Based on your narration I don't understand what you want. but it might be

RULE1+RULE2+RULE3+RUL4 AS TOTAL

Regarding the COUNT DISTINCT you must provide more details.