Avatar of budi saleh
budi saleh
 asked on

auto sum from another table in MySQL

Dear Experts,

I have two table as attcahed in the attachment file ( budget and expenses), but I dont know how to make the calculation for the remaining budget at the expenses table ( formula is on the attachment - yellow highlight),

I know MySQL would be different  from MS-excel in generating formula, but I needed badly how to input formula in mySQL for the remaining budget

Thank you,

Avatar of undefined
Last Comment
budi saleh

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

the code would look like something below:
select e.* 
    , b.total_budget
    , b.total_budget - (select sum(x.total_expenses)
                       from expenses x
                    where x.budget_id = e.budget_id
                        and x.date <= e.date 
                        ) remaining_budget  
  from expenses e
  join budget b
    on b.id = e.budget_id

Open in new window

the "issue" is that eventually you may have expenses on the same date, in which case you may have behaviour issue.
if the expenses ID is a numerical increasing value (for example), you can replace the date condition by the expense ID field.
budi saleh

Dear Guy,

Thank you for your answers,

I got stuck on the query that you have posted , because when I look on your query ,the table is inner join between  expenses and budget, but my tables is in separate tables and no join between those two tables,

Or should I join my expenses and budget table to get the remaining budget from two table, or maybe there is a simple query wthout joining the two table and have the remaining budget,

Thank you
Guy Hengel [angelIII / a3]

>and no join between those two tables,
there MUST be a join possible, otherwise how do you want to relate the bugdet with the expenses?
or is this a global thing (1 line in the budget table in that case)?

please clarify
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
budi saleh

Dear Guy,

Just to clarify,

I have two tables in mysql,

first is budget  table which contain two coloumn : budget_id and total budget, the budget table will not change ( its a fixed table)

the second is expenses table contain coloumn : expenses_id, date, total expenses

I want to have one coloumn showing the remaining budget based on the date wehere the formula is

 remaining budget = total budget - (total expenses (this date) + total expenses (previous date ))

Hope this can clarify to my question,

Thank you
Guy Hengel [angelIII / a3]

so, only 1 record in budget?
I mean, the expenses should be in regards to one specific budget, means that in expenses table, you should have a foreign key to the budget table.

if not, please clarify the data and the output with several records in budget table.
budi saleh

True..only one record for budget table, example:

Budget table:
Budget_id :1
Total budget :$600

Expenses table:
Id:  a
Date : 13 April 2015
Expenses : $100

Id: b
Date : 14 April 2015
Expenses: $500

Can I have one coloumn showing my remaining budget but ordered by date of expenses

Using budget and expenses example above :

Date                  remaining budget
13 April 2015    ($600 - $100 =$500)
14 April 2015    ($600 - $100 +$500 =0)

Hope this can clarify

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

then replace:
on b.id = e.budget_id
on 1=1
budi saleh


Thank you,

Now I have changed the tables to have connections between budget and expenses, but I still dont  have the remaining budget per date/per expenses id
For example :

budget table
budget_id = 1
total_budget =$3000

expenses table
Expenses_id = 1 ( auto increment)
budget_id = 1
Date = 12 April 2015
total_expenses = $1000

Expenses_id = 2 ( auto increment)
budget_id = 1
Date = 13 April 2015
total_expenses = $500

How can I get the value for each remaining budget (per date) i.e :

Remaining_budget ( coloumn)
Expenses_id : 1
Date : 12 April 2015
Remaining Budget : $ 2000

Expenses_id : 2
Date : 13 April 2015
Remaining Budget : $ 1500

Guy Hengel [angelIII / a3]

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
budi saleh

it return awesome...thank you guy
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy