working with case and counts

Database

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 ACOL BCOL C (Expected)
1ABCVALUE1
1DEFVALUE1
2ABCABC
3ABCABC
4DEFDEF
5ABCVALUE 2
5DEFVALUE 2
5GHIVALUE 2
6ABCVALUE 1
6DEFVALUE 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!

1 REPLY
Teradata Employee

Re: working with case and counts

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

cust_id, item

1

ABC

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

 

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;
1ABCVALUE1
1DEFVALUE1
2ABCABC
3ABCABC
4defdef
5ABCVALUE2
5DEFVALUE2
5GHIVALUE2
6ABCVALUE1
6DEFVALUE1

 

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