FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


mySQL going down when using SUM()





imagefree
This is my table:

Code:
CREATE TABLE IF NOT EXISTS `ledger` (
  `le_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `le_mid` tinyint(3) unsigned NOT NULL,
  `le_details` varchar(1000) NOT NULL,
  `le_received` varchar(10) NOT NULL DEFAULT '0',
  `le_paid` varchar(10) NOT NULL DEFAULT '0',
  `le_date` int(10) unsigned NOT NULL,
  PRIMARY KEY (`le_id`),
  KEY `le_date` (`le_date`),
  KEY `le_received` (`le_received`),
  KEY `le_paid` (`le_paid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


When i use the following query, mySQL shuts down and i have to manually restart it. Previously everything was fine and the query was giving correct results.

Code:
SELECT SUM(le_received) , SUM(le_paid) FROM ledger WHERE le_date <= '1288594799'
//Should return 1 Row, but fails

SELECT SUM(le_received) , SUM(le_paid) FROM ledger WHERE le_date < '1285916400'
//Should return 0 rows and works fine (doesnt crash)


There is only one row in the table. And everything seems to be fine but mysql is crashing. Please help me how to stop this?
Peterssidan
I think the problem is that you try to SUM a VARCHAR column. You may have to cast the value to an integer somehow to make that work. But why are you storing timestamps as VARCHAR? Why not store it as an unsigned integer?
Fire Boar
I don't think le_received and le_paid are timestamps, but a numeric quantity, since you never want to sum timestamps. Even so, they should still be either int columns with all stored values 100 times the actual value (so 3.50 is stored as 350), or float columns.
imagefree
Fire Boar wrote:
I don't think le_received and le_paid are timestamps, but a numeric quantity, since you never want to sum timestamps. Even so, they should still be either int columns with all stored values 100 times the actual value (so 3.50 is stored as 350), or float columns.


I am using VARCHAR because i am expecting FLOAT and INT values in it, but when i tried to define it as FLOAT it didnt work. That was the first time i was handling amounts so i didnt know what to do. VARCHAR worked fine and so i started using it.

Quote:
I think the problem is that you try to SUM a VARCHAR column. You may have to cast the value to an integer somehow to make that work. But why are you storing timestamps as VARCHAR? Why not store it as an unsigned integer?


No. Everything was working fine. Even i have uploaded the whole site to client's server. But now its creating problems on localhost. Online one is still showing results correctly.
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.