Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select order totals one-to-many values

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    A simpler SQL statement that produces the same result:

    SELECT SUM(ot.value) AS total_amount
FROM order AS o
JOIN order_totals AS ot ON o.id = ot.order_id
WHERE o.date_added = '2024-08-29'
  AND ot.code IN ('sub_total', 'deduction')

    Also, be aware that order is a reserved word in MySQL. I received an error when trying to create a table with that name:

    You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'order (
id int not null auto_increment primary key,
date_added date not null
)' at line 1

    Changing the table name to orders worked.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search