SQL Problems with query

Question

I am having problems with the following query:

SELECT Cliente.Nome, (Reserva.NumeroNoites) TotalNoites 
From Reserva 
inner join Cliente on Reserva.ID_Cliente = Cliente.ID_Cliente 
group by Reserva.NumeroNoites, Cliente.Nome

I’d like to select clients with the largest number of nights... However when doing my query... the result is

Cliente Barbosa : 1 noite;
Cliente Silva: 1 noite;
Cliente Silva: 2 noites;

Here is the problem... on the chart appears 2 times the same client ( Silva ) where the correct would appear only 1 time with the total of 3 Nights. Can someone help me?

Answer 1

The problem is that you are not adding up the total nights; what you are doing is just listing the nights and the customers (so if the customer has 10 reservations, it will be displayed 10 times). Try the query below:

SELECT Cliente.Nome, SUM(Reserva.NumeroNoites) as TotalNoites 
From Reserva 
inner join Cliente on Reserva.ID_Cliente = Cliente.ID_Cliente 
group by Cliente.Nome