Jump to content

SUM calculation not working !!


Skylight_lady

Recommended Posts

Can someone please tell me why this query is not working:

$query = "SELECT SUM((amount - Newamount) * months) AS TOTAL FROM mytable WHERE Newamount != '0.00'";

 

Even if i use:

$query = "SELECT SUM(amount - Newamount) AS TOTALSMALL FROM mytable WHERE Newamount != '0.00'";

 

It would return the right amount. But i want to do more calculations with the months added. Each row will have different months.

Link to comment
Share on other sites

In the table i have:

amount double(10,2) NOT NULL DEFAULT '0.00'
Newamount double(10,2) NOT NULL DEFAULT '0.00'
months int(10) NOT NULL DEFAULT '0'

 

I found another way around this by using php only with the following code "+=":

$Newamount += .......

 

This allows me to calculate each row to get its total. However, i want to know about this sql query as i never leave things alone without knowing the answer.

Link to comment
Share on other sites

No, forget it to change "double" to "decimal", it works just fine.

I've made exactly same table structure like yours, and this works fine.

$query = "SELECT SUM(amount - Newamount) AS TOTALSMALL FROM mytable WHERE Newamount != '0.00'";

 

Link to comment
Share on other sites

I want to get the SUM of "(amount - Newamount) * months" for all the rows from the table eg:

amount

Newamount

months

10.00

5.00

10

100.00

40.00

12

 

The first rows total will be 50.00 while the second rows total will be 720.00. Then the SUM of it all should be 770.00. I hope this clear enough !

Link to comment
Share on other sites

Skylight_lady: That was perfect, thank you. :)

Also, as jazzman1 pointed out, the query you posted earlier should have worked. Did you get any error messages, wrong amounts, or..? In other words, in what way "didn't it work"? If we get that piece of the puzzle as well, it should be trivial to find the solution.

 

PS: For future reference, when asking for help on a forum it's always a good idea to post what you want, what you're trying, what you're expecting, and what you get. Being really specific about each step, as you were just above with your last post. Now we know the first three, only missing the last one. ;)

Link to comment
Share on other sites

I want to get the SUM of "(amount - Newamount) * months" for all the rows from the table eg:

amount

Newamount

months

10.00

5.00

10

100.00

40.00

12

 

The first rows total will be 50.00 while the second rows total will be 720.00. Then the SUM of it all should be 770.00. I hope this clear enough !

 

And...something else, if you want to retrieve the current month of year as an integer from 1 - 12, you could create a new field with a "DATE" type and using a month sql function to retrieve it. 

Now this field will have much more functionality than before. Don't waste database resources.

Take a look at this:

amount

Newamount

created_at

10.00

5.00

2012-10-12

100.00

40.00

2012-12-08

 

SELECT SUM((
`tbl_name`.`amount` -`tbl_name`.`newAmount`
) * month(`tbl_name`.`created_at` ))
FROM `tbl_name`

Link to comment
Share on other sites

In his structure months is a number of months, not the 10th month.

Common..Jessie....don't kidding me  ;)

To write integers between 1 - 12, this guy use integer column type(10), signed.

@Christian, could you explain to him, what does mean, please ?

 

Link to comment
Share on other sites

I'm afraid jessirose is correct. The column months can be 32 .... Anyways to be more clear it has to be in the number of months and not a date from the users input.

 

Well, it seems like the query works for jazzman1 if he's getting the correct result. I'll have to double check in a few days what was causing me to get a completely different total without any errors. Maybe it was calculating two different queries at the same time and gave me a strange result then. Thanks for your help guys.

Link to comment
Share on other sites

In his structure months is a number of months, not the 10th month.

Common..Jessie....don't kidding me  ;)

To write integers between 1 - 12, this guy use integer column type(10), signed.

@Christian, could you explain to him, what does mean, please ?

 

Look at the freaking math OP is doing, and if you're going to try to use my name instead of my username, spell it right, it's RIGHT THERE.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.