CODEWITHSUNDEEP
sqlsql-serverstored-proceduressumaggregate
Ayo Adesina
Ayo Adesina

Reputation: 2395

How to return the calculated (aggregated) result of a SQL Server stored procedure

I'm trying to write a stored procedure that will return a number (an average) - this is what I have so far...

ALTER PROCEDURE [dbo].[sp_GetAverageRating]
    @RecipeNodeId int 
AS 
    SET NOCOUNT ON;

    (SELECT SUM(Rating) AS RatingTotal
     FROM dbo.RecipeRating
     WHERE RecipeNodeId = @RecipeNodeId)

    (SELECT 
         COUNT(Rating) AS RatingEntries 
     FROM dbo.RecipeRating 
     WHERE RecipeNodeId = @RecipeNodeId)

So this works and gives me

A.) Rating Total
B.) Number of Rating Entries

I want to return Rating Total / Rating Entries from the stored procedure.

Can anyone help me with the syntax please?

Upvotes: 2

Views: 169

Answers (1)

Greg Viers
Greg Viers

Reputation: 3523

Just use the AVG function:

SELECT AVG(Rating) as RatingTotal
FROM dbo.RecipeRating
WHERE RecipeNodeId = @RecipeNodeId

Upvotes: 2

Related Questions