Skylight_lady Posted August 15, 2012 Share Posted August 15, 2012 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 15, 2012 Share Posted August 15, 2012 Explain the table structure, please. $query = "EXPLAIN `mytable`" Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted August 15, 2012 Author Share Posted August 15, 2012 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 15, 2012 Share Posted August 15, 2012 Change "double" to "decimal". I think that type of table is not correct in this particular case, but I'm not sure 100%. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 15, 2012 Share Posted August 15, 2012 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'"; Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 15, 2012 Share Posted August 15, 2012 So, if I've understood you correctly, you want to GROUP the sums BY `months`? Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted August 16, 2012 Author Share Posted August 16, 2012 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 ! Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 16, 2012 Share Posted August 16, 2012 What a result do you expect to be? I got - 770, the query is: SELECT SUM(( `amount` - `newAmount` ) * `months` ) FROM `tbl_name` Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 16, 2012 Share Posted August 16, 2012 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 16, 2012 Share Posted August 16, 2012 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` Quote Link to comment Share on other sites More sharing options...
Jessica Posted August 16, 2012 Share Posted August 16, 2012 In his structure months is a number of months, not the 10th month. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 17, 2012 Share Posted August 17, 2012 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 ? Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted August 17, 2012 Author Share Posted August 17, 2012 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. Quote Link to comment Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 20, 2012 Share Posted August 20, 2012 If you want to debug SUMs, drop the SUM/GROUP BY and inspect individual rows. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.