KateMak KateMak - 2 months ago 5
SQL Question

Multiple joins, average on one table, count on another

I have four tables in a database: City, User, CityRating, CityGreeting. The CityRating table has the UserID and CityID as the PK, and those are FKs to the USer and City table. The CityGreeting table has no PK, but has the UserID and CityID as FKs (the idea is that a user can greet a city as many times as desired, but only rate a city once).

I am trying to write a query that will return the average rating of the city overall, as well as the times a specific user greeted the city:

select City.CityID, City.CityName, City.CityStateOrProvince,
ROUND(AVG(Cast(RateCity.Rating as float)), 2) as AverageRating,
(select COUNT(HelloCity.CityID) from HelloCity where HelloCity.UserID like '<guid>') as TimesVisited
from City
right join RateCity
on City.CityID = RateCity.CityID
right join HelloCity
on City.CityID = HelloCity.CityID
group by City.CityID, City.CityName,
City.CityStateOrProvince, City.CityCountry, City.CityImageUri


Even if I can get this to work as expected (which it currently is not) I feel like it is really messy. In terms of best practices, would it be better to write two queries? This operation would be performed in an api, not sure if the performance would be better on writing two seperate queries instead, or one complex one like this. Any insight on this or how to get the query to work as expected?

***EDIT: Added picture to clarify: Average Rating is the average of all users who rated, and TimesVisited is the amount of times one specific user has visited the city.

Added picture to clarify: Average Rating is the average of all users who rated, and TimesVisited is the amount of times one specific user has visited the city.

Answer

I believe you need to aggregate the tables, apart from city separately for this to work correctly:

select c.*, rc.AverageRating, coalesce(hc.TimesVisited, 0) as TimesVisited
from City c join
     (select CityId, ROUND(AVG(Cast(RateCity.Rating as float)), 2) as AverageRating
      from RateCity rc
      group by CityId
     ) rc
     on c.CityID = rc.CityID left join
     (select CityId, count(*) as TimesVisited
      from HelloCity hc
      where hc.UserID like '<guid>'
      group by CityId
     ) hc
     on c.CityId = hc.CityId;

Notes:

  • Table aliases make the query easier to write and to read.
  • I doubt you really mean right join. That would imply that there are CityIds in the other two tables that are not in City.
  • By doing the aggregation for each other table, you don't need an aggregation in the outer query.
  • I do think you want a left join for the HelloCity table, because not all cities might have visitors.
  • You might want a left join for the RateCity table as well, if not all cities have ratings.