title: MYSQL INNER JOIN AND GROUP BY
body: <p>I have a product table that holds product details like name, price, img and etc, and orders table where records orders. On the orders table, I store the only product_id from products and I want to group my results.
  For example in my products table, I have</p>
  
  <pre><code>product_id, product_name, product_price
  1           apple         10
  2           juice         5
  3           pineapple     7.5
  </code></pre>
  
  <p>As for my orders table for example, I have</p>
  
  <pre><code>order_id, product_id, quantity, date
  1         1           3         20.02.2020
  2         3           2         22.12.2019
  3         3           4         12.12.2020
  4         1           2         12.02.2020
  </code></pre>
  
  <p>I want to GROUP my ORDERS table by product name and quantity
  For example</p>
  
  <pre><code>product_name, quantity, price
  apple         5         50
  pineapple     6         45
  </code></pre>
  
  <p>And here is my query</p>
  
  <pre><code>SELECT 
  products.product_id, 
  products.product_name,
  products.product_price AS price,
  orders.quantity AS col,
  orders.by_date,
  (SELECT SUM(price * col)) AS total
  FROM products
  INNER JOIN orders ON products.product_id = orders.product_id
  GROUP BY products.product_id,   
       products.product_name,
       orders.order_id
  </code></pre>
  
  <p>And the result is not as excepted </p>
  
  <p>I'm not sure exactly what I'm doing wrong. Do I need to do a subquery?</p>