CODEWITHSUNDEEP
sqlsql-server
Bench
Bench

Reputation: 57

Each GROUP BY expression error in sql server

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!

Upvotes: 0

Views: 1128

Answers (3)

Middletone
Middletone

Reputation: 4270

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.

Upvotes: 0

Andrea C.
Andrea C.

Reputation: 11

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?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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

Upvotes: 1

Related Questions