Doubt about Group by

Question

using SQL Server

I have made the following select to select products purchased by customers:

select ITEMPEDIDO.IDPEDIDO, ITEMPEDIDO.QUANTIDADE, CLIENTE.NOME  
  from ITEMPEDIDO, cliente, pedido
  where PEDIDO.IDPEDIDO=ITEMPEDIDO.IDPEDIDO AND CLIENTE.IDCLIENTE = PEDIDO.IDCLIENTE
  order by ITEMPEDIDO.QUANTIDADE desc

outworking :

IDPEDIDO QUANTITY NAME

5 3 Adrina Domingues

1 1 Edivaldo Santana

2 1 José Antonio

3 1 Hugo Batista

3 1 Hugo Batista

4 1 Cristina Oliveira

1 1 Edivaldo Santana

2 1 José Antonio

now I intend to add the quantity items depending on the requested id, however this giving error.

select ITEMPEDIDO.IDPEDIDO, sum (ITEMPEDIDO.QUANTIDADE), CLIENTE.NOME  from ITEMPEDIDO, cliente, pedido

      where PEDIDO.IDPEDIDO=ITEMPEDIDO.IDPEDIDO AND CLIENTE.IDCLIENTE = PEDIDO.IDCLIENTE

      group by ITEMPEDIDO.IDPEDIDO

      order by ITEMPEDIDO.QUANTIDADE desc --FETCH NEXT 1 ROWS ONLY

error msg :

ORA-00979: not a GROUP BY Expression

Answer 1

You need to define the grouping expression in the SELECT clause.

It is necessary to use one of them: SUM, AVERAGE, COUNT, etc ... in this context the SUM

It is important to be aware that, to use in Group by, the field should also be listed in SELECT.

 select ITEMPEDIDO.IDPEDIDO, SUM(ITEMPEDIDO.QUANTIDADE) as Quantidade, CLIENTE.NOME from ITEMPEDIDO, cliente, pedido
 GROUP BY ITEMPEDIDO.IDPEDIDO, CLIENTE.NOME

Name that needs to be added in the GROUP BY clause, all fields present in the SELECT clause other than aggregation functions.

GROUP BY (Entity SQL)

Answer 2

Ola managed to solve as follows

select client.name, sum(itempedido.quantity) "Total purchase" from itempedido
Inner Join request on request.idrequest = itemped.idrequest Inner Join client on client.idclient = request.idrequest

group by pedido.idpedido, cliente.nome
order by sum(itempedido.quantidade) desc 

FETCH NEXT 1 ROWS ONLY;

Answer 3

Still need to add in the GROUP BY, the column CLIENTE.NOME

Would look like this:

select ITEMPEDIDO.IDPEDIDO, sum (ITEMPEDIDO.QUANTIDADE), CLIENTE.NOME  from ITEMPEDIDO, cliente, pedido
 where PEDIDO.IDPEDIDO=ITEMPEDIDO.IDPEDIDO AND CLIENTE.IDCLIENTE = PEDIDO.IDCLIENTE
 group by ITEMPEDIDO.IDPEDIDO, CLIENTE.NOME
 order by ITEMPEDIDO.QUANTIDADE desc

Answer 4

Every time you go to make the sum in a query you need to define what are the items you intend to add up

in your case you can use the sum for quantity and group per customer and request the query will be so:

select ITEMPEDIDO.IDPEDIDO,  CLIENTE.NOME sum (ITEMPEDIDO.QUANTIDADE) qte
from ITEMPEDIDO, cliente, pedido
group by ITEMPEDIDO.IDPEDIDO,  CLIENTE.NOME

As I don’t know your right table, I have no way to inform the relationship to eliminate duplicates