sqlodbcteradatateradata-sql-assistant

CASE WHEN or WHERE with new column creation, naming each category, and SUM AS or OVER the category (Teradata SQL)


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.


Solution

  • 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;