Need assistance with SQL query with error message subquery returns more than 1 row

Question

0.00/5 (No votes)

See more:

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 20-Jun-20 7:09am

MarissaReloaded

Updated 21-Jun-20 22:49pm

Add a Solution

2 solutions

Add a Solution

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

Herman<T>.Instance · Answer 1 · 2020-06-20T08:51:00

Solution 1

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

Posted 20-Jun-20 8:51am

Herman<T>.Instance

Comments

MarissaReloaded 20-Jun-20 18:16pm

Hi, Thanks for your feedback
Could you give an example?

Jörgen Andersson · Answer 2 · 2020-06-21T22:50:00

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

Need assistance with SQL query with error message subquery returns more than 1 row

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Need assistance with SQL query with error message subquery returns more than 1 row

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us