I have the following 5 CASE
and SUM
statements that work. I can create a category first, and then I can sum of this category.
However, given that I have several categories, my task is to wrap these in one statement, to avoid possible Category duplicates and shorten the code.
What I would like to be able to do, is to create an additional column with Category
and then SUM
by Category
in a single statement.
This is what I use now and what works:
--create a Category column
SELECT LandRisk, ProduktCode, ClassCode, Pd, Exposure
CASE
WHEN
ProduktCode IN (1,2)
AND Pd > 0.4694 THEN 'Bankrupt'
WHEN
LEFT(LandRisk,2) IN ('US')
AND ProduktCode IN (1,2)
AND ClassCode = 0 THEN 'US Bond'
ELSE NULL
END AS Category
FROM database.tableCreditRisk
ORDER BY Category, ProduktCode DESC;
--sum by category Bankrupt
SELECT
LandRisk, ProduktCode, ClassCode, Pd, Exposure,
SUM (Exposure)
AS sum_exposure
FROM database.tableCreditRisk
WHERE
ProduktCode IN (1,2)
and Pd > 0.4694
GROUP BY LandRisk, ProduktCode, ClassCode, Pd, Exposure
ORDER BY ClassCode DESC;
--sum by category US Bond
SELECT
LandRisk, ProduktCode, ClassCode, Pd, Exposure,
SUM (Exposure)
AS sum_exposure
FROM database.tableCreditRisk
WHERE
LEFT(LandRisk,2) IN ('US')
AND ProduktCode IN (1,2)
AND ClassCode = 0
GROUP BY LandRisk, ProduktCode, ClassCode, Pd, Exposure
ORDER BY ClassCode DESC;
--Bankrupt Category, Totals
SELECT
LandRisk, ProduktCode, ClassCode, Pd, Exposure,
SUM (Exposure)
OVER()
AS total_exposure
FROM database.tableCreditRisk
WHERE
ProduktCode IN (1,2)
and Pd > 0.4694
;
--US Bond Category, Totals
SELECT
LandRisk, ProduktCode, ClassCode, Pd, Exposure,
SUM (Exposure)
OVER()
AS total_exposure
FROM database.tableCreditRisk
WHERE
LEFT(LandRisk,2) IN ('US')
AND ProduktCode IN (1,2)
AND ClassCode = 0
;
And the following is what I seek to achieve - basically, to combine two or more CASE WHEN
and then SUM OVER()
or SUM AS
them, creating a Category column and summing by it. (the following does not work):
--Desirable shortened code, Sum over each Category
SELECT LandRisk, ProduktCode, ClassCode, Pd, Exposure,
SUM(CASE
WHEN
Pd > 0.4694
THEN 'Bankrupt' Else NULL) as total_exposure,
SUM(CASE
WHEN
LEFT(LandRisk,2) IN ('US')
and ClassCode = 0
THEN 'US Bond' Else NULL) as total_exposure
from database.tableCreditRisk
Where (Category='Bankrupt' or Category='US Bond') AND ProduktCode IN (1,2);
This is an example dataset:
| LandRisk | ProduktCode | ClassCode| Pd |Exposure|
| -------- | ------------ | -------- | ------- | ------ |
| US | 1 | 0 | 0,3 | 8 |
| AT | 2 | 0 | 0,5 | 3 |
| US | 1 | 0 | 0,3 | 4 |
| GB | 3 | 0 | 0,6 | 5 |
The result would look something like this:
| LandRisk | ProduktCode | ClassCode| Pd | total_exposure| Category |
| -------- | ------------ | -------- | ------- | ------------ | -------- |
| US | 1 | 0 | 0,3 | 12 | US Bond |
| AT | 2 | 0 | 0,5 | 3 | Bankrupt |
| GB | 3 | 0 | 0,6 | 5 | null |
I use Teradata Studio.
Three options:
--aggregated
SELECT
SUM (Exposure),
CASE
WHEN Pd > 0.4694 THEN 'Bankrupt'
WHEN LEFT(LandRisk,2) IN ('US')
and ClassCode = 0 THEN 'US Bond'
ELSE NULL
END AS Category
FROM database.tableCreditRisk
Where ProduktCode IN (1,2,8) and DayId = '2022-12-31'
GROUP BY Category
ORDER BY Category DESC;
--aggregated
SELECT
SUM(CASE When Pd > 0.4694 Then Exposure Else 0 End ) as Bankrupt
,
SUM(CASE WHEN LEFT(LandRisk,2) IN ('US')
and ClassCode = 0 THEN Exposure Else 0 End ) as USBond
from database.tableCreditRisk
Where ProduktCode IN (1,2,8) and DayId = '2022-12-31';
--more granular
SELECT LandRisk, ClassCode, ProduktCode, Pd, Exposure,
CASE
WHEN Pd > 0.4694 THEN 'Bankrupt'
WHEN LEFT(LandRisk,2) IN ('US')
and ClassCode = 0 THEN 'US Bond'
ELSE NULL
END AS Category
FROM database.tableCreditRisk
Where ProduktCode IN (1,2,8) and DayId = '2023-09-30'
GROUP BY Category, LandRisk, ClassCode, ProduktCode, Pd, Exposure
ORDER BY Category DESC;