I'm struggling to find the best way to get the total order value (sub-total - deduction) for a specific date or date range (for example today). I don't want shipping value included. The complexity is because the sub-total and deductions are one 'order' to many 'order_totals' with the code field.
What I have so far for my query:
SELECT
// Not sure what to do here with selecting sub-total and deductions
FROM `order_total` ot
INNER JOIN `order` o ON ot.order_id = o.id
WHERE DATE(o.date_added) = DATE(NOW());
order
id date_added
1 2024-08-29
2 2024-08-29
order_totals
id order_id code value
1 1 sub_total 500
2 1 deduction -35
3 1 shipping 10
4 1 total 475
5 2 sub_total 1102
6 2 deduction -65.3
7 2 shipping 0
8 2 total 1036.7
Expected Result (for the 2024-08-29)
total
1501.7
You can use this query to get the result you want simply using CASE WHEN:
SELECT
SUM(CASE WHEN ot.code = 'sub_total' THEN ot.value ELSE 0 END +
CASE WHEN ot.code = 'deduction' THEN ot.value ELSE 0 END) AS total
FROM `order` o
INNER JOIN `order_total` ot ON o.id = ot.order_id
WHERE DATE(o.date_added) = '2024-08-29'
AND ot.code IN ('sub_total', 'deduction');
And this is the example on dbfiddle: dbfiddle example