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,
D--table-budget-and-expense-.jpg
budi salehProject SchedulerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 salehProject SchedulerAuthor Commented:
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]Billing EngineerCommented:
>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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

budi salehProject SchedulerAuthor Commented:
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]Billing EngineerCommented:
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 salehProject SchedulerAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then replace:
on b.id = e.budget_id
by
on 1=1
budi salehProject SchedulerAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and I still am sure that my original query design shall return the data you are requesting, with just a small rename of b.id to b.budget_id:
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.budget_id = e.budget_id

Open in new window


what does it return for you?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
budi salehProject SchedulerAuthor Commented:
it return awesome...thank you guy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.