SQL Sum and Groupby Statement return wrong/weird number-CodePudding

Let's get to the point:

My first query:

select hotel, sum(adult) from bookings 
group by hotel;

and it shows the correct sum number, but whenever I try to join with other table:

select  b.hotel,    avg(r.hotelfacilities) as FacilitiesScore, 
                    avg(r.hotelservice) as ServiceScore, 
                    avg(r.hotelroom) as RoomScore, 
                    avg(b.rate) as PriceScore, 
                    sum(b.adult) as Persons
from bookings b join reviews r
on b.id = r.booking_id
group by b.hotel;

the number is weird and a lot bigger than it should be. Where did I do wrong?

CodePudding user response：

select  b.hotel,    
avg(r.hotelfacilities) as FacilitiesScore, 
avg(r.hotelservice) as ServiceScore, 
avg(r.hotelroom) as RoomScore, 
avg(b.rate) as PriceScore, 
sum(b.adult) as Persons
from bookings b join reviews r
group by b.hotel
on b.id = r.booking_id;

CodePudding user response：

You are joining booking with reviews so e.g. if there is one booking with two reviews it gives you two rows. Within group, that one booking became two rows which explains the weird (multiplied) values. One solution is to calculate the aggregates separately then join the results:

select *
from (
    select bookings.hotel
         , avg(b.rate) as PriceScore
         , sum(b.adult) as Persons
    group by bookings.hotel
) as booking_agg
join (
    select bookings.hotel
         , avg(reviews.hotelfacilities) as FacilitiesScore
         , avg(reviews.hotelservice) as ServiceScore, 
         , avg(reviews.hotelroom) as RoomScore, 
    from bookings
    join reviews on bookings.id = reviews.booking_id
    group by bookings.hotel
) as booking_review_agg on booking_agg.hotel = booking_review_agg.hotel