CODEWITHSUNDEEP
sqlaverage
Chris Kristiansen
Chris Kristiansen

Reputation: 11

SQL: Using AVG function on multiple selected tables but only for one column and grouped by that one column

I am trying to run an SQL query. I've joined multiple tables using the JOIN function so that I have all the data I need in columns (and only those I need).

This function works:

SELECT city, score FROM guests
JOIN delighted_surveys ON name = customer_name  
JOIN reservations ON guests.id = reservations.guest_id  
JOIN listings ON reservations.listing_id = listings.id 
JOIN properties ON listings.property_id = properties.id  
JOIN postal_addresses ON properties.id = postal_addresses.postally_addressable_id   
AND postal_addresses.postally_addressable_type = 'Property'

So essentially, I'm pulling the columns "CITY" and "SCORE" from the table "Guests" and a host of other joined tables. This works.. I get all the different scores for each city listed out.

GOAL: What I want to get, however, is the average score per city. I have tried using the AVG function, and I can get the average for the scores only. But I cannot get the average score per city. I keep getting syntax errors.

WHAT I'VE TRIED SO FAR: I've tried using "DISTINCT" and also "AVG" in conjunction with "GROUP BY" in multiple ways. I've also tried adding more "SELECT" statements in the query but that also results in syntax errors.

Essentially, the results I'm getting look like this:

   CITY       SCORE 
Oslo           9 
Copenhagen     10 
Oslo           8
Amsterdam      7
Copenhagen     6

and I want them to look like this, displaying the average for each city:

   CITY     AVERAGE SCORE
Copenhagen       8.0
  Oslo           8.5
Amsterdam         7

PS: Every single input has a data point (there is a score for each city in all cases).

Thanks in advance for any help with this! :)

Upvotes: 1

Views: 802

Answers (1)

mikeb
mikeb

Reputation: 11267

Try this:

SELECT city, AVG(score) FROM guests
JOIN delighted_surveys ON name = customer_name  
JOIN reservations ON guests.id = reservations.guest_id  
JOIN listings ON reservations.listing_id = listings.id 
JOIN properties ON listings.property_id = properties.id  
JOIN postal_addresses ON properties.id = postal_addresses.postally_addressable_id   
AND postal_addresses.postally_addressable_type = 'Property'
GROUP BY city

You use your aggregate (AVG SUM or whatever) in the select, and all other fields must be in the group by. That is how you use aggregation, you do not need distinct.

Upvotes: 1

Related Questions