I am building a stored procedure in MS SQL SERVER 2012. I want to display average level of current day.
CREATE PROCEDURE [dbo].[AverageP]
@UserID INT
AS
BEGIN
SELECT DAY(GETDATE()), AVG(Level) AS AvgLevel
FROM tab1
WHERE UserID = @UserID
GROUP BY DAY(GETDATE())
END
But i got this error:
Msg 164, Level 15, State 1, Procedure AverageP, Line 9 Each GROUP BY expression must contain at least one column that is not an outer reference.
Please, help me to solve this error!
You want a where clause, not group by. Something like this:
BEGIN
SELECT CAST(GETDATE() as DATE), AVG(Level) AS AvgLevel
FROM tab1
WHERE UserID = @UserID AND
tabl.SOMEDATEFIELD >= cast(GETDATE() as DATE) and
tab1.SOMEDATEFIELD < cast(GETDATE() + 1 as DATE)
END
It's complaining because you're not grouping by anything that is in tab1, but by the day of the current date.
Note that Day() is the number of the current day, like today it's 22 because it's May 22nd. If you're looking to look for the average for all Fridays you'll want Weekday() instead.
You probably don't mean to group at all if you just want the average level for all 22nds of the month across the data in your tab1. Is there a date column in tab1 that you mean to get the day of, and then in the where column say that the day of getdate() equals the day of that date column?
Actually you should be able to run this without a group at all.
SELECT DAY(GETDATE()) as [Day], AVG(Level) AS AvgLevel
FROM tab1
WHERE UserID = @UserID
But as Gordon points out we think you just want to filter by day. Either way the grouping is superfluous.
