Sum price from products to order-CodePudding

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

CodePudding user response：

SELECT customers.customer_id, customers.first_name, 
       customers.last_name, customers.email,
       orders.order_id, orders.date_order_placed,
       SUM(products.price * orders_products.quantity) total_order_sum
FROM orders
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products USING (product_id) 
LEFT JOIN customers USING (customer_id) 
/*
    WHERE orders.order_id = ?
or
    WHERE orders.order_id IN (?, ?, ...)
and also maybe
      AND customers.customer_id = ?
*/
GROUP BY customers.customer_id, customers.first_name, 
         customers.last_name, customers.email,
         orders.order_id, orders.date_order_placed
ORDER BY order_id DESC

CodePudding user response：

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

CodePudding user response：

SELECT 
    order_id,
    date_order_placed,
    CONCAT(first_name, ' ', last_name) AS Customer,
    SUM(price) AS Total,
    status
FROM
    order_table
        LEFT JOIN
    o_product_table USING (order_id)
        LEFT JOIN
    product_table USING (product_id)
        LEFT JOIN
    c_table USING (customer_id)
WHERE
    order_id = 20001
ORDER BY order_id DESC;