Skip to main content

How to fetch the row total (and unit total), including the discount in Magento 2

· 3 min read

Magento database structure can be very complex. This high complexity is especially true for the sales_order tables. These tables contain many columns with different numerical values, so fetching the data you need can be challenging.

In this article, we will show how to fetch the row total and the unit total, including discount, including or excluding tax, from the sales_order_item table in the Magento 2 database. We will show how to do that using an SQL query or programmatically.

Subtracting the discount from the row_total​

The Magento row_total column does not include the discount. Therefore, we need to subtract it manually. The final formula depends on whether you must fetch the data including or excluding tax.

Row total including tax​

Use the following formula to get the row total including tax and discount:

SQL​

SELECT row_total_incl_tax - discount_amount FROM magento.sales_order_item;

PHP​

$item->getRowTotalInclTax() - $item->getDiscountAmount()

Row total excluding tax​

Use the following formula to get the row total excluding tax and discount:

SQL​

SELECT row_total - discount_amount FROM magento.sales_order_item;

PHP​

$item->getRowTotal() - $item->getDiscountAmount()

Row total vs unit total​

It can also be helpful to fetch the unit total with a discount in Magento 2. The difference between row total and unit total is that unit total represents the amounts per one unit of item.

Example​

For example, say that the customer bought two bags, each costing $32, and he got a 10% discount on them.

So the row total in this example is $32x2=$64, and the row total with a discount is $64 - 10%x$64 = $57.6

The unit total, on the other hand, is $32, and the unit total with a discount is $32-10%x$32 = $28.8

Fetching the unit total with a discount​

We need more calculations to fetch the unit total with a discount. That is because Magento 2 doesn't store the discount amount per unit, only per row. Therefore, we must divide the discount_amount column by the qty column to get the discount amount per unit.

Unit total including tax​

Use the following formula to get the unit total, including tax and discount in Magento:

SQL​

SELECT price_incl_tax - (discount_amount / qty) FROM magento.sales_order_item;

PHP​

$item->getPriceInclTax() - ($item->getDiscountAmount() / $item->getQty())

Unit total excluding tax​

Use the following formula to get the unit total, excluding tax and discount in Magento:

SQL​

SELECT calculation_price - (discount_amount / qty)  FROM magento.sales_order_item;

PHP​

$item->getCalculationPrice() - ($item->getDiscountAmount() / $item->getQty())

To sum up​

In this article, we showed how to fetch the row total and the unit total in Magento with a discount, including or excluding tax. We showed how to fetch that data directly from the database using SQL query or programmatically using PHP code in Magento.

info

This code was tested on Magento CE 2.4.6, 2.4.7