Group function does not work

Asked

Viewed 58 times

0

I have two tables.

  1. Table: Pedido

    • Columns: IDPEDIDO, CLIENTE, DT_PEDIDO, FORMA_PGMT, CLIENTE_ID.

  2. Table: Itens_Pedido

    • Columns: PEDIDO_ID, QT_MERCADORIAS, MERCADORIA, MERCADORIA_ID.

I need to create a query to know which product is the most sold in January and February 2015. I made the query below but she is returning me this error:

"not a single-group group Function"

Consultation:

    Select max(sum(ITENS_PEDIDO.QT_MERCADORIA)),
           ITENS_PEDIDO.MERCADORIA
      From ITENS_PEDIDO
      Join PEDIDO
        On ITENS_PEDIDO.PEDIDO_ID = PEDIDO.IDPEDIDO
     Group By ITENS_PEDIDO.MERCADORIA_IDMERCADORIA,
           ITENS_PEDIDO.MERCADORIA,
           PEDIDO.DT_PEDIDO
    Having PEDIDO.DT_PEDIDO Between to_date('01/01/2015', 'DD/MM/YYYY')
                                And to_date('28/02/2015', 'DD/MM/YYYY');
sql database query oracle oracle11c

  • According to that answer in Soen you are using columns that are being used in aggregate functions in GROUP BY. Try redoing your query without including ITENS_PEDIDO.QT_MERCADORIA at GROUP BY;

  • This works... the problem is that if I don’t put ITENS_PEDIDO.QT_MERCADORIA it only shows the best selling quantity and does not show the product name.

  • Try to remove the max(), create an alias like this sum(ITENS_PEDIDO.QT_MERCADORIA) as total_mercadoria to use order by total_mercadoria desc limit 1.

  • It did not work a result 50 that I can not understand why, the sum will never give 50

1 answer

0

Try to simplify your query:

Select sum(ITENS_PEDIDO.QT_MERCADORIA),
       ITENS_PEDIDO.MERCADORIA
  From ITENS_PEDIDO
       Join PEDIDO
            On ITENS_PEDIDO.PEDIDO_ID = PEDIDO.IDPEDIDO
Where PEDIDO.DT_PEDIDO Between to_date('01/01/2015', 'DD/MM/YYYY')
                           And to_date('28/02/2015', 'DD/MM/YYYY')
 Group By ITENS_PEDIDO.MERCADORIA_IDMERCADORIA,
          ITENS_PEDIDO.MERCADORIA;

What has been done:

  • Removal of the aggregation function MAX: redundant in this case;
  • Placement of the filter criterion for the clause WHERE: the HAVING should only be used when the desired criterion involves a summary result;
  • Column removal PEDIDO.DT_PEDIDO of GROUP BY: I deduce by the fact that this field is not selected, the summarization must be only by product, and not product/date.

Browser other questions tagged sql database query oracle oracle11c

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