0

I have this code

 $orderModel = Mage::getModel('sales/order');
    $ordercollection = $orderModel->getCollection()
        ->setPageSize(20)
        ->setCurPage(1)
        ->join(array('item' => 'sales/order_item'), 'main_table.entity_id = item.order_id');
    $ordercollection->addFilterToMap('large_total', 'SUM(subtotal_invoiced)');

    $ordercollection->getSelect()->columns('SUM(subtotal_invoiced) as large_total');
    $ordercollection->getSelect()->where('SUM(subtotal_invoiced) >= 200');
    $ordercollection->getSelect()->group('main_table.customer_email');

If I take out the line

    $ordercollection->getSelect()->where('SUM(subtotal_invoiced) >= 200');

then it seems to work fine. I am not sure what I doing wrong here. Thanks!

1

In MySQL aggregate functions (like SUM) cannot be used as part of a WHERE clause because the values aren't calculated early enough. Instead there is an additional clause called a HAVING clause.

$ordercollection->getSelect()->columns('SUM(subtotal_invoiced) as large_total');
$ordercollection->getSelect()->group('main_table.customer_email');
$ordercollection->getSelect()->having('SUM(subtotal_invoiced) >= 200');
0

SELECT A.CurrentProfit - B.CurrentProfit FROM kikingo_mt4.AccountLogs_Daily A JOIN ( SELECT CurrentProfit FROM kikingo_mt4.AccountLogs_Daily WHERE Date IN (select min(Date) from kikingo_mt4.AccountLogs_Daily where Date between current_date() and current_date() -7 ) ) B ON A.MT4Account = B.MT4Account WHERE A.Date = MAX(Date)

//////// Error Code: 1111. Invalid use of group function ////////

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.