How to sum over the updated value by affecting the sum of the values in a different column in the sum of the values in the column?

I want to get result as SumC

i tried sum with case functions but i couldnt get it

CREATE TABLE `New_Temp`.`Temp` (
  `day_no` INT NULL,
  `rate` INT NULL,
  `amount` INT NULL);

INSERT INTO `New_Temp`.`Temp` (`day_no`,`rate`,`amount`)
VALUES  (1,0,50)
        ,(2,0,40)
        ,(3,6,0)
        ,(4,0,20)
        ,(5,0,10)
        ,(6,8,0)
        ,(7,0,5)

CodePudding user response：

You have to use a recursive query for such things.

Here is a query that does what you want for MySQL 8:

WITH RECURSIVE sum_with_perc AS (
    SELECT *, CAST(amount AS FLOAT) AS c_sum
    FROM Temp
    WHERE day_no = 1

    UNION ALL
    
    SELECT 
        Temp.*,
        CASE
            -- SUM   percents
            WHEN Temp.rate > 0 THEN sum_with_perc.c_sum   Temp.amount   ((sum_with_perc.c_sum   Temp.amount) * Temp.rate) / 100
            ELSE sum_with_perc.c_sum   Temp.amount
        END
    FROM Temp
    JOIN sum_with_perc ON Temp.day_no = sum_with_perc.day_no   1
)
SELECT 
    day_no,
    rate,
    amount,
    ROUND(c_sum, 1) AS SumC
FROM sum_with_perc;

See output on dbfiddle
Read more about recursive queries in MySQL

Here is a query that does what you want for PostgreSQL:

WITH RECURSIVE sum_with_perc AS (
    -- select first row
    SELECT *, "VALUE_A"::numeric AS c_sum
    FROM recs
    WHERE day_no = 1

    UNION ALL
    
    -- recursive part goes here
    SELECT 
        recs.*,
        CASE
            -- SUM   percents
            WHEN recs."RATE_A" > 0 THEN sum_with_perc.c_sum   recs."VALUE_A"   ((sum_with_perc.c_sum   recs."VALUE_A") * recs."RATE_A") / 100
            ELSE sum_with_perc.c_sum   recs."VALUE_A"
        END
    FROM recs
    JOIN sum_with_perc ON recs.day_no = sum_with_perc.day_no   1
),
-- recs CTE is just for simulating real table
recs AS (
    SELECT *
    FROM (
        VALUES
            (1, 0, 50),
            (2, 0, 40),
            (3, 6.0, 0),
            (4, 0, 20),
            (5, 0, 10),
            (6, 8.0, 0),
            (7, 0, 5)
    ) s (day_no, "RATE_A", "VALUE_A")
)
SELECT 
    day_no,
    "RATE_A",
    "VALUE_A",
    ROUND(c_sum, 1) AS "SumC"
FROM sum_with_perc;

See output of this query on dbfiddle

Read more about recursive queries

UPD: See the PostgreSQL query port for MySQL 8 on dbfiddle