SUM calculation not working !!

[Skylight_lady]

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.

[jazzman1]

Explain the table structure, please.

$query = "EXPLAIN `mytable`" 

[Skylight_lady]

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.

[jazzman1]

Change "double" to "decimal".

I think that type of table is not correct in this particular case, but I'm not sure 100%.

[jazzman1]

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'";

 

[Christian F.]

So, if I've understood you correctly, you want to GROUP the sums BY `months`?

[Skylight_lady]

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 !

[jazzman1]

What a result do you expect to be?

I got - 770, the query is:

SELECT SUM((
`amount` - `newAmount`
) * `months` )
FROM `tbl_name`

[Christian F.]

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.

[jazzman1]

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`

[Jessica]

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

[jazzman1]

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 ?

 

[Skylight_lady]

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.

[Jessica]

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.

[fenway]

If you want to debug SUMs, drop the SUM/GROUP BY and inspect individual rows.