How to display the name of the product and its total number in the table? Here is the table structure enter image description here

  • amount for orders you mean, or what? - teran 1:21 pm
  • Yes, for example, you need the quantity of goods sold "Coat 99" and the amount - Aslero
  • one
    SELECT name , SUM ( count ), SUM ( cost ) FROM products GROUP BY name - wirtwelt
  • thanks a lot - Aslero

1 answer 1

Apparently, we have a table of customer order details. In the simple case, to calculate the quantity, you will need a query

 select name, sum(`count`) as q from orders group by name

However, it can be assumed that the name field in the table is presented to save the name of the product exactly as it was when it was ordered, and that, in general, the position name may change. Similarly with the price, but the price is logical, that is changing. (but the cost field, in the presence of price & count seems redundant, although it may take some discounts into account.)

Therefore, grouping by name cannot be done; we can get different lines for the same product with different names. In this regard, it is easier to attach a table of products and display the actual name at the current time.

 SELECT p.id, p.name , sum(o.count) AS qnt , sum(o.cost) AS total FROM orders as o LEFT JOIN products AS p ON (p.id = o.product_id) GRPOUP BY p.id, p.name ORDER BY p.name

In general, this query will be equivalent to a variant with a reverse sequence of tables and an internal join (intersection) from products inner join orders .
If you want to see the full list of products, taking into account those where there are 0 orders, you will need an external connection from products left join orders .