Database
Fan

## working with case and counts

Hi Friends,

I am new to teradata and I'm trying to learn via the blogs here. i have a scenario which i am unable to create.

below is the table-

 COL A COL B COL C (Expected) 1 ABC VALUE1 1 DEF VALUE1 2 ABC ABC 3 ABC ABC 4 DEF DEF 5 ABC VALUE 2 5 DEF VALUE 2 5 GHI VALUE 2 6 ABC VALUE 1 6 DEF VALUE 1

if the count of values in COL B is 2, then COL C should have VALUE 1, if the count is more than 3, then key in Value 2.

however if the count is 1, then put in the value from COL B.

I would really appreciate if you can help me with this question.

Thanks!

## Re: working with case and counts

There are 2 solutions.  I made a "test" table that looks like this:

cust_id, item

 1 ABC 1 DEF 2 ABC 3 ABC 4 def 5 ABC 5 DEF 5 GHI 6 ABC 6 DEF

if you are looking to count the number of total rows for each cust_id (you aren't concerned about non-distinct item names), then you can use an OLAP function inside of a CASE statement:

```select cust_id
,item
,CASE WHEN count(item) over (partition by cust_id) = 2 THEN 'VALUE1' WHEN count(item) over (partition by cust_id) >=3 THEN 'VALUE2' ELSE item END
FROM test;```
 1 ABC VALUE1 1 DEF VALUE1 2 ABC ABC 3 ABC ABC 4 def def 5 ABC VALUE2 5 DEF VALUE2 5 GHI VALUE2 6 ABC VALUE1 6 DEF VALUE1

If you need to count the number of distinct items for each cust_Id (if there are two rows for cust_id= 1 with item = 'ABC' and you want that to just count as one). then you need to use a derived table since you can't use the DISTINCT clause in OLAP functions:

```SELECT a.cust_id
, a.item
, CASE WHEN b.item_count = 2 THEN 'VALUE1' WHEN b.item_count >=3 THEN 'VALUE2' ELSE item END
FROM test a
INNER JOIN
(select cust_id
,count(distinct(item)) as item_count
from test
group by 1) b
on a.cust_id = b.cust_id```