Search code examples
sqlaggregateteradata

Teradata SQL aggregate with conditions

I have the below table with data for one month for some customers, and I need to aggregate this table based on conditions for the values of every column,

The conditions

  • If the customer's stability have >= 2 of "Very Unstable" status then return 1 else 0
  • In Value_1 if the customer has at least one record with the value: 1 along the entire month then return 1 else 0
|Cust_ID|Date    |Stability     |Value_1|
|-------+--------+--------------+-------|
|123    |3/1/2022|Unstable      |1      |
|123    |3/2/2022|Very Unstable |0      |
|123    |3/3/2022|Stable        |1      |
|123    |3/4/2022|Ver Stable    |NULL   |
|123    |3/5/2022|Unstable      |NULL   |
|123    |3/6/2022|Very Unstable |0      |
|123    |3/7/2022|Unstable      |0      |
|123    |3/8/2022|Very Unstable |0      |
|…      |…       |…             |…      |
|123    |3/31/2022|Very Unstable|0      |

to be the result table like that:

|Cust_ID|Stability|Value_1|
|-------+---------+-------|
|123    |1        |1      |
Solution

  • This seems to match your description:

    SELECT Cust_ID
   -- If the customer's stability have >= 2 of "Very Unstable" status 
   -- then return 1 else 0
  ,CASE
     WHEN COUNT(CASE WHEN Stability = 'Very Unstable' THEN 1 END) >= 2 
     THEN 1 
     ELSE 0
   END AS STABILITY_COUNT
  -- In Value_1 if the customer has at least one record with
  -- the value: 1 along the entire month then return 1 else 0
  ,MAX(Value_1) Value_1_m
FROM ST_TABLE
GROUP BY 1