How to use CASE statement to update data (as fractions) in the table?

Query Table:

Different Fruits Total Fruits Fruits Fraction
3 5
2 6
2 2
4 6
1 1
6 6
6 16
4 20

Updated Table:

Different Fruits Total Fruits Fruits Fraction
3 5 3/5
2 6 2/6
2 2 2/2
4 6 4/6
1 1 1/1
6 6 6/6
6 16 6/6
4 20 4/6

There is a total of 6 different fruits. However, the total fruits per record can be greater than 6.

I want to use a case statement such that:

WHEN Total Fruits < 6 THEN Fruits Fraction = Different Fruits/Total Fruits
ELSE Fruits Fraction = Different Fruits/6

I do not want to simplify the fraction, Probably I think it would use character datatype.

Please help.

Thanks in advance.

>Solution :

You could try this:

SELECT "different fruits", "total fruits", 
  CASE 
    WHEN "total fruits" > 6 THEN "different fruits"||'/'||'6'
    ELSE "different fruits"||'/'||"total fruits"
    END AS fruits_fraction
FROM your_table

db<>fiddle link here

Leave a Reply