Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brubaker
Starting Member
15 Posts |
 Posted - 2001-11-23 : 23:13:21
|
| I would appreciate any help on the following query. It appears quite complicated to me and I can't seem to figure it out. All of the information is located in one table, tableRestaurants, with the exception of the name of the city, which is in tableCities. I'm using a foriegn key in the tableRestaurants to point to the correct city.I want to want to assign assign a rating to each city based based on the following formula:(Avg(rating) * 10 * (number of restaurants/150)) Plus (2 * number of 5 star restaurants) Plus (number of 4 star restaurants).The rating is equal to the number of stars a restaurant has (1-5).I then want to rank them (like in the Reader Challenge 2).I can compute the avg rating and the count of restaurants, but I cannot seem to figure out how to also do the two additional counts (number of 4 and 5 star restaurants) in the same select statement.Additional problems:1) In the portion of the formula where it takes the number of restaurants and divides by 150, I only want to perform this part of the equation if there are more than 150 restaurants. The idea here is to give bonus points if there are more than 150, but not take points away if there are less. So the result of that computation should be >= 1.2) I need to sort by the score or rank. This seems straight forward, but I can't seem to get it to work. (If someone could also explain why sometimes the results of Aggregate functions need to be put in single quotes when put in a ORDER BY statement, that would also be great, i.e. ORDER BY 'Rank'.)In the end, I would like the statement to look like:SELECT cityName, Score, RankFROM *******ORDER BY RankcityName is already in the tableCities and the Score and Rank need to be computed from information soley found in tableRestaurants.Thanks in advance for your time and thought.Chris |
|
|
|
|
|
|
|
A Challenging Query