How to substrate number from the sum of columns of a table based on the id-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.