Home > Enterprise >  Is it possible to change the value of a column if it has more than one result (using GROUP BY)?
Is it possible to change the value of a column if it has more than one result (using GROUP BY)?

Time:05-18

Is it possible to change the result only if there's more than one?

My query brings this result:

Date Code Description Amount
01/01 1001 Produt A 1234
02/01 1001 Produt A 2345
03/01 1001 Produt A 3456

If I have only one produt, that's fine.

But if I have more than one, it shows like this:

Date Code Description Amount
01/01 1001 Produt A 1234
02/01 1001 Produt A 2345
03/01 1001 Produt A 3456
01/01 1002 Produt B 4321
02/01 1003 Produt B 5432
03/01 1004 Produt B 6543

The question is: Is it possible to change values only if I have more than one produt in my query?

Something like this; if I have products A and B, it should show:

Date Code Description Amount
01/01 Various Various 5555
02/01 Various Various 7777
03/01 Various Various 9999

CodePudding user response:

You can use case expressions, looking at the count of (optionally distinct) values in each column; for example:

select some_date,
  case when count(distinct code) > 1
       then 'Various'
       else to_char(max(code))
  end as code,
  case when count(distinct description) > 1
       then 'Various'
       else max(description)
  end as description,
  sum(amount) as amount
from your_table
group by some_date
order by some_date;

db<>fiddle

  • Related