SQL - create new col based on value of other column group by third column-CodePudding

I have this table

Id	item	type
A	itemA1	X
A	itemA2	X
B	itemA1	X
B	itemA2	X
B	itemA3	Y

And i would like to create new indicator which contains the information about if the Id contains only item of type X or only tpye Y or both like this :

Id	Indicator
A	Only X
B	Both

Thanks in advance for your help

CodePudding user response：

If you group by Id you can check the number of distinct types with a CASE expression:

SELECT Id,
       CASE COUNT(DISTINCT type)
         WHEN 1 THEN CONCAT('Only ', MAX(type))
         WHEN 2 THEN 'Both'
       END Indicator
FROM tablename
GROUP BY Id;

CodePudding user response：

This will cover if both item are 'Y' as well:


with table_with_sample_data as (
select 'A'  as Id ,'itemA1' as item,  'X' as type union all
select  'A',  'itemA2',   'X' union all
select  'B',  'itemA1', 'X' union all
select  'B',  'itemA2', 'X' union all
select  'B',  'itemA3', 'Y' union all
select  'C', 'itemA1',  'Y' union all
select  'C', 'itemA2',  'Y'
)

SELECT Id,
       CASE WHEN COUNT(DISTINCT type) = 1  and MAX(type) = 'X' THEN CONCAT('Only ', MAX(type))
            WHEN COUNT(DISTINCT type) = 1 AND MAX(type) = 'Y' THEN CONCAT('Only ', MAX(type))
            WHEN COUNT(DISTINCT type) = 2 THEN 'Both'
       END Indicator 
FROM table_with_sample_data

GROUP BY Id;

CodePudding user response：

Consider below generic approach

select id, 
  if(count(distinct type)=1,'Only ','') || string_agg(distinct type) indicator
from your_table
group by id