How do I extract the number of orders with 5 different items? I have this query but it is coming out a different result than expected

Asked

Viewed 69 times

-4

SELECT DISTINCT(pedidos.id_pedidos),produtos.nome_produto
FROM pedidos
    INNER JOIN itens_dopedido ON itens_dopedido.id_pedido=pedidos.id_pedidos
    INNER JOIN produtos ON produtos.id_produto=itens_dopedido.id_produto
WHERE nome_produto = (SELECT (COUNT(DISTINCT(id_pedido)) >= 5));

Results are coming out with 4 different orders and 1. Can anyone explain to me what is going on? Because I don’t understand.

mysql sql database

  • What is the purpose of your query?

  • Consider using the clause HAVING. It is the typical problem to which it applies.

2 answers

3


If you only want the order quantity that has more than 5 items, just use one subquery that counts the amount of itens_dopedido and be compared with the number you want:

SELECT COUNT(1) AS quantidade
  FROM pedidos p
 WHERE (SELECT COUNT(1)
          FROM itens_dopedido i
         WHERE i.id_pedido = p.id_pedidos) >= 5

  • I’ll do it here, thank you very much :)

  • @vivianearaujo if the answer has met you do not forget to mark it as correct, so people with similar doubts may benefit from your question.

1

In fact you are doing some things wrong, in Where you are comparing product name_a random sum. You also need to use the requested itens_do_table, I made a very simple example with a subselect, I just don’t know in your table which is the "id_item_request", follows example how select should be done:

SELECT pedidos, nome_produto, qtd_itens_diferentes

  FROM(

    SELECT ped.id_pedidos, p.nome_produto, 
    COUNT(itp.id_item_pedido) AS qtd_itens_diferentes

    FROM pedidos ped

        INNER JOIN itens_dopedido itp
            ON itp.id_pedido = ped.id_pedidos

        INNER JOIN produtos p
            ON p.id_produto = itp.id_produto

    GROUP BY ped.id_pedidos, p.nome_produto 
  )

WHERE qtd_itens_diferentes > 5

  • Instead of SUM it shouldn’t be COUNT?

  • That’s right rs, adjusted, I hope I helped you.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.