Fix “Unknown column SUM(IFNULL…” in Magento

This is just a quick fix post in case you are experiencing the exception “Unknown column ‘SUM((IFNULL” when trying to log in to the Magento admin backend. Below you find the full exception stack trace for reference:

a:5:{i:0;s:1073:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SUM((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(ma' in 'field list', query was: SELECT `SUM((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table`.`base_to_global_rate)` AS `lifetime`, `AVG((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table`.`base_to_global_rate)` AS `average` FROM `sales_flat_order` AS `main_table` WHERE (main_table.status NOT IN('canceled')) AND (main_table.state NOT IN('new', 'pending_payment'))";i:1;s:4503:"#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `SUM((IF...', Array)
#4 lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `SUM((IF...', Array)
#5 lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `SUM((IF...', Array)
#6 lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `SUM((IF...', Array)
#7 app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `SUM((IF...', Array)
#8 lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
#9 app/code/core/Mage/Adminhtml/Block/Dashboard/Sales.php(65): Varien_Data_Collection_Db->load()
#10 app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Adminhtml_Block_Dashboard_Sales->_prepareLayout()
#11 app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#12 app/code/core/Mage/Adminhtml/Block/Dashboard.php(54): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...')
#13 app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Adminhtml_Block_Dashboard->_prepareLayout()
#14 app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#15 app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...', 'dashboard')
#16 app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('adminhtml/dashb...', 'dashboard')
#17 app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#18 app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#19 app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#20 app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#21 app/code/core/Mage/Adminhtml/Controller/Action.php(275): Mage_Core_Controller_Varien_Action->loadLayout(NULL, true, true)
#22 app/code/core/Mage/Adminhtml/controllers/DashboardController.php(40): Mage_Adminhtml_Controller_Action->loadLayout()
#23 app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_DashboardController->indexAction()
#24 app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#25 app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#26 app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#27 app/Mage.php(684): Mage_Core_Model_App->run(Array)
#28 index.php(87): Mage::run('', 'store')
#29 {main}";s:3:"url";s:70:"/index.php/admin/dashboard/index/key/78e1f84097adc7e831f279f919f0d1a6/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}

Check Zend Library version

The solution in this case is pretty easy. Make sure that your Zend library conforms to your Magento version. It’s easiest to just remove the lib/Zend folder and use a fresh Zend library version corresponding to your Magento version. As always, make sure to clear var/cache afterwards. This has been tested in Magento CE 1.9+.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *