How to extract products and their total quantity in one sql query?

Question

MySQL

The next task is to extract the goods and their total quantity (In this case, those with color = green)
One product may have a lot of prices or not at all, you need to extract the minimum and only 1!
Now the cnt column is the number of prices. Here is the problem.

SELECT DISTINCT
  prd.id,
  prd.name,
  ROUND( MIN(prc.price), 2) AS price,
  COUNT(prd.*) as cnt

FROM products prd
LEFT JOIN price prc ON prc.product_id = prd.id

WHERE prd.color = green
GROUP BY prd.id
ORDER BY price DESC

UPD:
All this is necessary in order not to send a separate COUNT (*) request, but there are a lot of goods in the database.

Answer 1

Hello
As I understand it, you have one total quantity for all products with color = green. Therefore, it makes no sense to pull it in the same request. Pull it with another request.

SELECT  COUNT(prd.id) AS cnt FROM products AS prd WHERE prd.color = green.

Answer 2

SELECT DISTINCT
  prod.id,
  prod.name,
  prod.producer_id,
  prod.rating,
  prod.data,
  pr.price,
  pr.valute_id

FROM products prod
  -- в результате тут будет массив.
  -- join будет обьединять не с таблицой, а с массивом !!!
  -- сделал чтобы вытянуть valute_id минимальной цены
  LEFT JOIN (
        SELECT
          ROUND(price, 2) AS price,
          valute_id,
          product_id
        FROM price
        WHERE moderation = 1
           && type_product = 1
        ORDER BY price
      ) pr ON pr.product_id = prod.id

WHERE prod.archive = 0
   && (prod.color_id = 1 || prod.color_id = 43)
   && pr.price >= 2000 && pr.price <= 10000

GROUP BY prod.id
ORDER BY relevant DESC
LIMIT 0, 20

Answer 3

If I understand everything correctly,
in the first query you need to replace
COUNT(prd.*) as cnt
with
COUNT(*) as cnt
and
GROUP BY prd.id
with
GROUP BY prd.id, prd.name
It will be like this:
SELECT DISTINCT
prd.id ,
prd.name,
ROUND( MIN(prc.price), 2) AS price,
COUNT(*) as cnt
FROM products prd
LEFT JOIN price prc ON prc.product_id = prd.id
WHERE prd.color = green
GROUP BY prd. id, prd.name
ORDER BY price DESC