Add a label column as a result in the group aggregation for a specific value

Question

I have a set of data that I group by "product_group_id", "group_name" and "class". Names of products for this group are different.

CREATE TABLE rule (
   product_id integer
 , product_group_id integer
 , group_name text
 , class text
 , name text
);

INSERT INTO rule VALUES
  (1, 4, 'fruit', '0010', 'apple')
, (2, 4, 'fruit', '0010', 'cherry')
, (3, 4, 'fruit', '0010', 'pineapple')
, (4, 4, 'fruit', '0010', 'tomato')
, (5, 4, 'fruit', '0010', 'banana')
, (6, 4, 'fruit', '0010', 'peach')

During the select I want to check the "name" list and if "tomato" name exists in the set I will add "invalid" value or "valid" to the new "label" column. The result for the data example above would be (tomato name exists):

product_group_id | group_name | class | label   |
-----------------+------------+-------+---------+
               4 |      fruit | 0010  | invalid |
             

or it would be valid if "tomato" name does not exist in the set:

product_group_id | group_name | class | label |
-----------------+------------+-------+-------+
               4 |      fruit | 0010  | valid |

So far I can get both groups valid and invalid if tomato appears in the set.

select r.product_group_id, r.group_name, r.class, 
  case
    when r.name='tomato' then 'invalid'
    else 'valid'
  end label
from rule r
group by r.product_group_id, r.group_name, r.class, label;

product_group_id | group_name | class |   label |
-----------------+------------+-------+---------+
               4 |      fruit | 0010  |   valid |
               4 |      fruit | 0010  | invalid |

Answer 1

You need to use an aggregated function e.g. max to check the tomato and not group by on the label

select r.product_group_id, r.group_name, r.class, 
  case
    when max(case when r.name ='tomato' then r.name end) = 'tomato' then 'invalid'
    else 'valid'
  end label
from rule r
group by r.product_group_id, r.group_name, r.class

For extended sample date (with two groups)

INSERT INTO rule VALUES
  (1, 4, 'fruit', '0010', 'apple')
, (2, 4, 'fruit', '0010', 'cherry')
, (3, 4, 'fruit', '0010', 'pineapple')
, (4, 4, 'fruit', '0010', 'tomato')
, (5, 4, 'fruit', '0010', 'banana')
, (6, 4, 'fruit', '0010', 'peach')
, (7, 5, 'foo', '0020', 'bar');

the result is as follows

product_group_id|group_name|class|label  |
----------------+----------+-----+-------+
               5|foo       |0020 |valid  |
               4|fruit     |0010 |invalid|