[SOLVED] SUM and Subtract in mysql

[nitation]

Hello guys,

 

I believe it is possible to SUM all the columns in a MYSQL table. How do i UNSUM or Subtract all the columns in a mysql table also.

 

Thanks in advance

[kenrbnsn]

What do you mean? What are you subtracting the values from?

 

Ken

[nitation]

@ken

 

If i have three columns like this;

 

column1 =100

column2 =200

column2 =300

 

to sum them, i believe the query is like this

select SUM(column) AS Total FROM table_name where xx='xx'

 

This will add the values together and give me

 

600.

 

Good. My question now is, i am inserting a new value which is 1000, how do i minus it from the last three values.

 

Thanks in advance.

[.josh]

well you can't do select subqueries inside a modify query so you're gonna have to break it down into 2 separate queries.  The first query would be the one you already have.  The second query will be a plain run of the mill insert query where you just do 1000-$Total as the value ($Total being the retrieved info from the first query).

[nitation]

Let me break what am trying to do .

 

A user's account was credited with the below number by the administrator in the admin area;

 

row1 =100

row2 =200

row3 =5000

 

This was added together using (SUM) in the mysql query to get the Total balance, which is 5800.

 

The user then try to transfer the amount credited. This works fine. The problem is, if i update the table where the amount was credited, to derive the user's balance, this update all the amount in the rows.

 

If i insert the amount the user transferred into the same table on a new row, on the balance page, it will SUM all the values in the row. Now instead of the balance decreasing, it is forever increasing.

 

How do i derive the amount transferred by the user and echo it on the balance page. Do i keep it on a separate table or what. Please help

 

This is running me off.

 

Thank you in advance.

[smstromb]

Crayon Violent is right. Just make 2 separate queries. Store the sum in a variable like $total and then subtract the amount you want with simple php arithmetic. Store the answer in another variable, and then insert that final variable into the table of your choice using a second query. Hope that helped.