Custom column for reports

Question

I am building a custom reports module. I need to add a column which will represent a percent based on the corresponding sale column and total for that column. Bellow is the select I am using for getting all needed data.

public function addTotalSalesByDepartment($frm = '', $to = '')
{
    $qtyOrderedTableName = $this->getTable('sales/order_item');
    $qtyOrderedFieldName = 'qty_ordered';

    $productIdTableName = $this->getTable('catalog/product');
    $productIdFieldName = 'product_id';

    $productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_varchar';
    $eavAttributeTable = $this->getTable('eav/attribute');


    $frm = date('Y-m-d G:i:s', strtotime($frm));
    $to = date('Y-m-d G:i:s', strtotime($frm . '+' . '1 day'));

    if ($frm != '' && $to != '') {
        $dateFilter = " AND `order`.created_at BETWEEN '{$frm}' AND '{$to}'";
    } else {
        $dateFilter = "";
    }

    $this->getSelect()->reset()->from(
      array('order_items' => $qtyOrderedTableName),
      array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})", 
                    'base_price_total' => "SUM(order_items.price * order_items.{$qtyOrderedFieldName})",
                    'sms_dept_id' => "pei.value", 
                    'sale_percentage' => '**THIS IS THE VALUE I NEED TO CALCULATE**'
   );

   $_joinCondition = $this->getConnection()->quoteInto(
       'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
   );

   $_joinCondition .= $dateFilter;

   $this->getSelect()->joinInner(
       array('order' => $this->getTable('sales/order')),
       $_joinCondition,
       array()
   );

   $this->getSelect()
       ->joinInner(array('e' => $productIdTableName),
           "e.entity_id = order_items.{$productIdFieldName}")
       ->order('pei.value ASC')
       ->group('pei.value')
       ->having('SUM(order_items.qty_ordered) > 0');

   $this->getSelect()
       ->joinInner(
           array('pei' => $productEntityIntTable),
           "e.entity_id = pei.entity_id",
           array())
       ->joinInner(
           array('ea' => $eavAttributeTable),
           "pei.attribute_id=ea.attribute_id AND ea.attribute_code='sms_dept_id'",
           array());

    Mage::log('SQL: '.$this->getSelect()->__toString());
    return $this;
}

EDIT: I put as you can see in my comment. How can I deal with undefined index warning for my custom columns when trying to sort after these fields? It is related to my original question and I hope it is fine to put it here.

Answer 1

Correct e if I'm wrong but I don't think that the calculation will work due to (my?)SQL limitations. Either you use a subselect or you use the result set as a data source for another select query.

Example for 1) select *, select (select sum(sales) from ...) / (select sum(x) from ...)

Example for 2) select *, ((foo/bar) * 100) as percentage from your result_set with foo and bar as separate columns in your result set.

But I'm not sure if it will work properly with Magento's DBAL.