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

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.

CodePudding user response：

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

CodePudding user response：

There is no need for a CASE expression.
You can do it with MIN() scalar function:

SELECT DifferentFruits, TotalFruits,
       DifferentFruits || '/' || MIN(TotalFruits, 6) FruitsFraction
FROM tablename;

Or, if you want to update the table:

UPDATE tablename
SET FruitsFraction = DifferentFruits || '/' || MIN(TotalFruits, 6);

See the demo.