title: How do I get an average of one column based on the number of records returned in my query results?
body: <p>I'm currently writing a Stored Procedure where an itinerary is generated based off of the input given by the user.  I'd like to get the average of all values in <code>AVG_HOTEL_RATING</code>, but that's dependent on the number of records returned in the results.  Current code:</p>
  <pre><code>ALTER PROCEDURE [dbo].[NewHotelItinerary]
  
  @Username NVARCHAR(50),
  @User_ID INT,
  @Destination NVARCHAR(50),
  @Pet_Friendly BIT,
  @Hotel_Price_Min NUMERIC(4) = NULL,
  @Hotel_Price_Max NUMERIC(4) = NULL,
  @Trip_Start_Date DATETIME = NULL,
  @Trip_End_Date DATETIME = NULL,
  @Number_Of_Nights NUMERIC(2)
  
  AS
  BEGIN
      SET NOCOUNT ON;
      
          BEGIN
          DECLARE @TRIP_COST NUMERIC(4)
  
          SELECT @TRIP_COST = (SELECT HOTEL_PRICE / @Number_Of_Nights FROM HOTEL_DETAIL)
              SELECT HOTEL_NAME, HOTEL_DESCRIPT, AVG_HOTEL_RATING, LOCATION.LOCAT_CITY
                  FROM HOTEL_DETAIL
                  JOIN LOCATION ON HOTEL_DETAIL.LOCAT_ID = LOCATION.LOCAT_ID
                  WHERE HOTEL_PRICE &gt;= @Hotel_Price_Min AND HOTEL_PRICE &lt;= @Hotel_Price_Max AND LOCAT_CITY = @Destination
  
  
              INSERT INTO ITINERARY_LOG (USER_ID, TRIP_START_DATE, TRIP_END_DATE, TRIP_TOTAL, TRIP_AVG_RATING, DESTINATION)
              SELECT @User_ID, @Trip_Start_Date, @Trip_End_Date, @TRIP_COST, @Destination
  
          END
      END
  
  </code></pre>
  <p>I understand how to use the function AVG(), but not quite sure how to close the gap between using that function in my code and determining the average depending on the number of records.  For example, if only 3 hotels are returned because those are the only hotels that fall under the user's price input, I'd want to add those 3 together and then divide by 3.  This of course could change if the number of results returned is 5, 10, 15, etc.</p>
  <p>Here's some mock data from my HOTEL_DETAIL table:
  <a href="https://i.stack.imgur.com/aJ3GE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aJ3GE.png" alt="enter image description here" /></a></p>
  <p>Essentially, if all 5 of those hotels get returned in the query results, I want to INSERT the AVG() of those hotel's <code>AVG_HOTEL_RATING</code> from <code>HOTEL_DETAIL</code> values into <code>ITINERARY_LOG</code>'s <code>TRIP_AVG_RATING</code> column.  Again to clarify, these are the hotels that are returned from the query results, <strong>not</strong> the entire HOTEL_DETAIL table.</p>
  <p>Any assistance anyone could give would be greatly appreciated.</p>