I am researching customers and their shopping habits using SQL in Teradata.
Say I have the following information:
Table wklytrans
CustNm GroceryStore # WklyTrxns Sum WklyTrxns CustType
Jack Publix 2 $70 Publix
Ruby Kroger 1 $15 Both
Moe Kroger 4 $120 Kroger
Ruby Publix 1 $20 Both
I would like to create a new table that classifies them as follows:
CustNm CustType # PubTrxns Sum PubTrxns # KroTrxns Sum KroTrxns
Jack Publix 2 $70 NULL NULL
Ruby Both 1 $20 1 $15
Moe Kroger NULL NULL 4 $120
The code I'm currently trying to run is as follows:
CREATE MULTISET VOLATILE TABLE custclass AS(
SEL
CustNm,
CustType,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN # WklyTrxns
WHEN CustType IN 'Publix' THEN # WklyTrxns
END AS # PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN Sum WklyTrxns
WHEN CustType IN 'Publix' THEN Sum WklyTrxns
END AS Sum PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN # WklyTrxns
WHEN CustType IN 'Kroger' THEN # WklyTrxns
END AS # KroTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN Sum WklyTrxns
WHEN CustType IN 'Kroger' THEN Sum WklyTrxns
END AS Sum KroTrxns
FROM wklytrans
)
WITH DATA
PRIMARY INDEX (CustNm)
ON COMMIT PRESERVE ROWS;
This is what is currently being outputted:
CustNm CustType # PubTrxns Sum PubTrxns # KroTrxns Sum KroTrxns
Jack Publix 2 $70 NULL NULL
Ruby Both 1 $20 NULL NULL
Ruby Both NULL NULL 1 $15
Moe Kroger NULL NULL 4 $120
How can I get my code to combine Ruby's transactions onto the same line so that it looks like the output I desire?
Any help is appreciated!
Aucun commentaire:
Enregistrer un commentaire