CODEWITHSUNDEEP
mysqlsqljoinaggregate-functions
irregularexpressions
irregularexpressions

Reputation: 262

SQL Sum() - 0 in field excluding row from aggregation?

Consider the following query:

$query = "
SELECT a_orders.id, a_orders.billing,
   SUM(a_order_rows.quant_refunded*a_order_rows.price*((100-a_orders.discount)*.01)) as refund_total,
   SUM(a_order_rows.quant*a_order_rows.price*((100-a_orders.discount)*.01)) as order_total 
FROM a_order_rows JOIN a_orders 
ON a_order_rows.order_id = a_orders.id 
WHERE a_order_rows.quant_refunded > 0 
GROUP BY a_orders.id, a_orders.billing 
ORDER BY a_orders.id DESC 
LIMIT 50";

The two uses of SUM() are attempting to aggregate the order total and the total amount that has been refunded. They are coming out correctly when when the quant_refunded field is not 0... for example take this data (excluding primary key, item_id for simplicity's sake, assuming each row is a unique item):

Table: a_order_rows
Fields: order_id, quant, quant_refunded
1, 1, 1
1, 2, 1
2, 1, 1
2, 1, 0

In the case of "order 1" the two aggregations are different and behave as expected. But for "order 2" the two numbers are coming out the same - both numbers are what I am expecting refund_total to be. It appears that the row with "0" in quant_refunded is being excluded from the order_total aggregation.

Hopefully this is explained thoroughly enough. Please let me know if you need more info and I will revise. THANKS!

Upvotes: 0

Views: 1661

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

$query = "
SELECT a_orders.id, a_orders.billing,
   SUM(a_order_rows.quant_refunded*a_order_rows.price*((100-a_orders.discount)*.01)) as refund_total,
   SUM(a_order_rows.quant*a_order_rows.price*((100-a_orders.discount)*.01)) as order_total 
FROM a_order_rows JOIN a_orders 
  ON a_order_rows.order_id = a_orders.id 
GROUP BY a_orders.id, a_orders.billing 
HAVING MAX(a_order_rows.quant_refunded) > 0 
ORDER BY a_orders.id DESC 
LIMIT 50";

Change that to a HAVING clause. If any quant_refunded rows are > 0, the HAVING MAX will retain it.

Upvotes: 1

Related Questions