auto sum from another table in MySQL

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

Select allOpen 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.

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

>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

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

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.

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

Thanks

then replace:
on b.id = e.budget_id
by
on 1=1

Guy,

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

Thanks

it return awesome...thank you guy