how to use the SUM within an ORDER BY (mysql)

Asked

Viewed 121 times

1

I have the following select in mysql:

SELECT DISTINCT
    a.id,
    a.unidade,
    a.posicao,
    a.nome,
    a.peso
FROM
    produtos a,
    produtos_pedidos b
WHERE
    a.id = b.id_produto
    and b.id_pedido IN (13,12,11)
ORDER BY a.nome

Good with it I have the products of orders 13,12,11 without repeating in alphabetical order. Good the problem and who wanted to put it in order of quantity, I tried to make the following select:

SELECT DISTINCT
    a.id,
    a.unidade,
    a.posicao,
    a.nome,
    a.peso
FROM
    produtos a,
    produtos_pedidos b
WHERE
    a.id = b.id_produto
    and b.id_pedido IN (13,12,11)
ORDER BY SUM(b.qtd)

However it returns me only 1 result. Does anyone know what I might be doing wrong.

mysql
  • 3

    What are you trying to do with this order? It doesn’t make much sense to sort by grouper without using GROUP. If you want quantity order, the sum makes even less sense. It would be easier to do it alone SUM b.qtd, or else GROUP BY b.id_request. I would suggest [Edit] the question and put the problem you want to solve, and not just the way you are trying.

1 answer

1


Try it like this:

SELECT DISTINCT
    a.id,
    a.unidade,
    a.posicao,
    a.nome,
    a.peso,
    sum(b.qtd) quant
FROM
    produtos a,
    produtos_pedidos b
WHERE
    a.id = b.id_produto
    and b.id_pedido IN (13,12,11)
GROUP BY 
    a.id,
    a.unidade,
    a.posicao,
    a.nome,
    a.peso
ORDER BY
    a.nome

Browser other questions tagged mysql

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