Click here to Skip to main content
15,867,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two tables, and I would like to update the running balance of expenses by retrieving the allocation(budget) based on matching voteid

I'm using phpmyadmin mysql 

* Apache/2.4.43 (Win64) OpenSSL/1.1.1g PHP/7.4.6
* Database client version: libmysql - mysqlnd 7.4.6
* PHP extension: mysqli Documentation curl Documentation mbstring Documentation
* PHP version: 7.4.6
* Server type: MariaDB


Table 1

| voteid | allocation |
+--------+------------+
| 1      | 50000      |
| 2      | 10000      |
| 3      | 34000      |
| 4      | 70000      |
Table 2

    | expenditureid | voteid | expenses |
    +---------------+--------+----------+
    | 1             | 2      | 300      |
    | 2             | 2      | 650      |
    | 3             | 4      | 900      |
    | 4             | 4      | 1200     |
    | 5             | 3      | 34000    |
Expected result

+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| expenditureid | voteid | expenses | balance (running difference) | calculation |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 10000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 1             | 2      | 300      | 9700                         | (10000-300)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 2             | 2      | 650      | 9050                         | (9700-650)                                          |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              |                                                     |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 70000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 3             | 4      | 900      | 69100                        | (70000-900)                                         |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 4             | 4      | 1200     | 67900                        | (69100-1200)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              |                                                     |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
|               |        |          |                              | 34000                                               |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+
| 5             | 3      | 4000     | 30000                        | (34000-4000)                                        |
+---------------+--------+----------+------------------------------+-----------------------------------------------------+


What I have tried:

SELECT allocation 
FROM vote 
INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
GROUP BY vote.voteid;

SET @csum = (SELECT allocation 
             FROM vote 
             INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
             GROUP BY vote.voteid);

UPDATE expenditure 
SET balance = (@csum = @csum - expenses) OVER (partition BY voteid ORDER BY expenditureid) AS balance;
Posted
Updated 21-Jun-20 22:49pm

The part of the query
SQL
SET @csum = (SELECT allocation 
             FROM vote 
             INNER JOIN expenditure ON vote.voteid = expenditure.voteid 
             GROUP BY vote.voteid);

will return mpre than 1 rows.

You have to refine that statement so you will have maximum 1 rows.
Other option is using an IN clause to process multiple rows
 
Share this answer
 
Comments
MarissaReloaded 20-Jun-20 18:16pm    
Hi, Thanks for your feedback
Could you give an example?
You are thinking procedurally.
With SQL it's best to think in sets.
And you should also consider keeping the data normalized, so the need to update the balance is a bad idea, that should be queried when needed. Database normalization - Wikipedia[^]

Take a look at the following query and adjust as needed:
SELECT  voteid
       ,allocation
       ,SUM(expenses) AS expenses
       ,allocation - SUM(expenses) AS Balance
FROM    vote 
JOIN    expenditure ON vote.voteid = expenditure.voteid
GROUP BY voteid,allocation
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900