Question 271285 in PostgreSQL Question posted in
The official documentation can be found here.

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

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 |
Tags:

2

Answers


  1. 0

    You can do the following. Count product_group_ids that exist in your table based on your where clause inside a case statement. If count = 0 return ‘invalid’.

    select r.product_group_id, r.group_name, r.class, 
  case 
    when 
    (select count(rule.product_group_id) from rule where rule.name = 'tomato' and rule.product_group_id = r.product_group_id and rule.group_name = r.group_name and rule.class = r.class) = 0 then 'invalid'
    -- r.name='tomato' then 'invalid'
    else 'valid'
  end as label
from rule r
group by r.product_group_id, r.group_name, r.class;

    this will give you:

    product_group_id group_name class label
    4 fruit 10 valid

    and if you add another record
    (7, 5, 'pasta', '0010', 'foo');

    and try tomato

    select r.product_group_id, r.group_name, r.class, 
  case 
    when 
    (select count(rule.product_group_id) from rule where rule.name = 'peach' 
     and rule.product_group_id = r.product_group_id and rule.group_name = r.group_name and rule.class = r.class) = 0 then 'invalid'
    -- r.name='tomato' then 'invalid'
    else 'valid'
  end as label
from rule r
group by r.product_group_id, r.group_name, r.class;

    you will get:

    product_group_id group_name class label
    4 fruit 10 valid
    5 pasta 10 invalid
    Login or Signup to reply.
  2. 0

    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|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search