Group function does not work

Question

Group function does not work

Navigation

#1 by (0 votes)

0

I have two tables.

Table: Pedido

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

Table: Itens_Pedido

Columns: PEDIDO_ID , QT_MERCADORIAS , MERCADORIA , MERCADORIA_ID .

I need to create a query to know which product sold the most in the months of January and February 2015. I did the following query but it is returning this error:

"not a single-group group function"

Inquiry:

    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 oracle query oracle11c

asked by anonymous 05.12.2015 / 20:22

1 answer

Reset Cell Phone Open only one instance

score 0 · Answer 1

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 was done:

Removing aggregate function MAX : redundant in this case;
Placing the filter criterion for the WHERE clause: HAVING should only be used when the desired criteria involves a result of the summarization;
Removing the column PEDIDO.DT_PEDIDO from GROUP BY : I assume that this field is not selected, the summarization should be only by product, not product / data.