Question 113122 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to substrate number from the sum of columns of a table based on the id

I have a table named deposit, below

dep_id deposit_amount comp_id
1 100 1
2 100 1
3 100 1

When I run the query below I get the next updated table which is not what I want :

query = em.createNativeQuery("UPDATE deposit SET deposit_amount = (SELECT SUM(deposit_amount) - 50) WHERE comp_id = :comp_id");

query.setParameter("comp_id", comp_id);

The updated table after the above query

dep_id deposit_amount comp_id
1 50 1
2 50 1
3 50 1

But I want when I substract 50 or any amount it should get the sum of the columns and minus the amount from the sum not from each column. Below

dep_id deposit_amount comp_id
1 83.3 1
2 83.3 1
3 83.3 1

Because the sum is 300, and 300-50 = 250

Please how should I do this?

Tags:

2

Answers


  1. 0

    Using a common table expression, you can use this query. Get the total deposit amount per comp_id. Then join this new table (called total) to deposit on comp_id. Subtract 50 from the sum.

    WITH total as(
select  comp_id, 
        sum(deposit_amount) as total
from 
        deposit 
group by 
        comp_id
  )
select dep.dep_id, 
       ttl.total - 50 as deposit_amount, 
       dep.comp_id
from
    deposit dep
inner join
    total  ttl
on 
   dep.comp_id = ttl.comp_id

    Sample:

    dep_id deposit_amount comp_id
    1 250 1
    2 250 1
    3 250 1
    Login or Signup to reply.
  2. 0

    You should compute deposit amount in a separate query, then join back your two tables on matching "comp_id" value

    WITH cte AS (
    SELECT DISTINCT comp_id,
                    SUM(deposit_amount) OVER(PARTITION BY comp_id) AS amount
    FROM deposit
)
UPDATE deposit
INNER JOIN cte
        ON deposit.comp_id = cte.comp_id
SET deposit_amount = cte.amount - 50
WHERE deposit.comp_id = :comp_id;

    In your final query it should look like:

    query = em.createNativeQuery("WITH cte AS (SELECT DISTINCT comp_id, SUM(deposit_amount) OVER(PARTITION BY comp_id) AS amount FROM deposit) UPDATE deposit INNER JOIN cte ON deposit.comp_id = cte.comp_id SET deposit_amount = cte.amount - 50 WHERE deposit.comp_id = :comp_id");

query.setParameter("comp_id", comp_id);

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search