[SOLVED] Sum price from products to order

Issue

I want to order my orders with the total price that it gets from products table. And show the customer details with it. This is how my tables look like:

Orders table

order_id customer_id date_order_placed status
20001 1 2022-03-11 Unpaid

order products table

product_order_id order_id product_id quantity
1 20001 1 1
2 20001 2 1

Products table

product_id name description price
1 Apple This is an apple 15
2 Pear This is a pear 30

Customers table

customer_id first_name last_name email
1 John Doe [email protected]

This is my SQL I tried

SELECT * 
FROM orders 
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products USING (product_id) 
LEFT JOIN customers USING (customer_id) 
WHERE order_id = 20001 
ORDER BY order_id DESC

The result I get is this and what I want I also described in the snippet

#customers {
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
}

#customers td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}

#customers tr:nth-child(even){background-color: #f2f2f2;}

#customers tr:hover {background-color: #ddd;}

#customers th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}
<!--- CSS BY W3schools -->
<h3>Result i get</h3>
<table id="customers">
  <tr>
    <th>Order id</th>
    <th>Date placed</th>
    <th>Customer</th>
    <th>Total price</th>
    <th>Status</th>
  </tr>
  <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;15</td>
    <td>Unpaid</td>
  </tr>
    <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;30</td>
    <td>Unpaid</td>
  </tr>
</table>

<h3>Result i want</h3>
<table id="customers">
  <tr>
    <th>Order id</th>
    <th>Date placed</th>
    <th>Customer</th>
    <th>Total price</th>
    <th>Status</th>
  </tr>
  <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;45</td>
    <td>Unpaid</td>
  </tr>
</table>

So instead that the orders with the same order id are invidual i want to add the prices of the same order and make it one total price i already tried this

SELECT * 
FROM orders
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products sum(price) as total_price USING (product_id) 
LEFT JOIN customers USING (customer_id) 
WHERE order_id > ? 
ORDER BY order_id DESC

but with no success

Solution

You need to make SUM on products.price * order_products.quantity and GROUP BY on order_products.order_id

SELECT
    SUM(products.price * order_products.quantity) AS total_order_price,
    orders.*,
    customers.*
FROM
    orders
    LEFT JOIN order_products ON orders.order_id = order_products.order_id
    LEFT JOIN products ON order_products.product_id = products.product_id
    LEFT JOIN customers USING orders.customer_id = customers.customer_id
WHERE
    order_id = 20001 
GROUP BY
    order_products.order_id 
ORDER BY
    order_id DESC

Read more about GROUP BY modifier:
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

Answered By – Valeriu Ciuca

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.