I'm building a custom report on the basis of database.

I need to know how to get 'Grand Total' that is in 'sales/order' (in other words sales_flat_order) table by order items (from 'sales/order_item', sales_flat_order_item table).

I need a formula to get this value using the database and not using Magento methods.

I am building MySQL query

Am I right when I say that

base_grand_total =  (IFNULL(base_total_canceled,0)) +  (IFNULL(base_hidden_tax_amount,0) - IFNULL(base_hidden_tax_refunded,0)) + IFNULL(base_total_offline_refunded,0) + IFNULL(base_total_online_refunded,0) +  ( IFNULL(base_subtotal,0) - IFNULL(base_subtotal_canceled,0) - IFNULL(base_subtotal_refunded,0)) + (IFNULL(base_tax_amount,0) - IFNULL(base_tax_canceled,0) - IFNULL(base_tax_refunded,0)) + (IFNULL(base_shipping_amount,0) - IFNULL(base_shipping_canceled,0) - IFNULL(base_shipping_refunded,0)) + (IFNULL(base_discount_amount,0) - IFNULL(base_discount_canceled,0) - IFNULL(base_discount_refunded,0)) ?
  • Are you using MySQL query ? if so please post here May 6, 2016 at 8:24
  • Thank you! I'm sorry. I had to immediately indicate this May 6, 2016 at 8:30
  • can you post that query here ? Basic query should look like: SELECT * FROM sales_flat_order as sfo INNER JOIN sales_flat_order_item as sfoi ON sfo.entity_id = sfoi.order_id WHERE sfoi.order_id = 42 May 6, 2016 at 8:34
  • Thank you for your help! I can write sql query. I need to know how to calculate base_grand_total using Subtotal, Tax, Shipping and other.. May 6, 2016 at 8:48

1 Answer 1


You can pass the order_entity_id and get the grand total value

function get_order_details($order_entity_id)
$getsales = mysql_query("SELECT grand_total FROM `sales_flat_order` WHERE `entity_id`='$order_entity_id'");
if(mysql_num_rows($getsales) == 1)
return mysql_fetch_object($getsales);
elseif(mysql_num_rows($getsales) > 1)
return $getsales;
return FALSE;
  • Thank you! But I need calculate "grand total" by order items. I group products according to different parameters, so I need exactly the formula for calculating this value. I can use table sales_flat_order_item and some values (such as 'shipping...') from sales_flat_order May 6, 2016 at 8:19
  • A more specific question - how to calculate the 'grand total' on the base of the other fields of the table sales_flat_order ? May 6, 2016 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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