Group By Quandry

God I hate the simple stuff...

I'm trying to get stats for user counts on an app. I have a 'login time' var which is datetime. I am trying to convert this to date (SQL 2008 R2), I really didn't expect the code below to work, but in fact IT DOES!!! Can anyone explain why? I was certain I would get the dreaded outer reference error!

select CONVERT(date,CONVERT(char(10),LoginTime,111)) lDate,COUNT(userID) lCount
FROM UserHist
where LoginTime >= DATEADD(month,-3,getdate())
group by CONVERT(date,CONVERT(char(10),LoginTime,111))
order by lDate


Thanks Folks!
Chaste SwedgeAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> Each GROUP BY expression must contain at least one column that is not an outer reference.

You will get this error if you have any Constant values in the GROUP BY clause..
But in the example which you have posted in the question, you don't have any constant values and hence it should not error out..

But in this example

select (3*3) as Nine, COUNT(*)
from UserHist
group by (3*3)

you have a constant value 3*3 = 9 and hence it can't be included in the GROUP BY clause..
Hope this clarifies
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
ORDER BY Clause will support column aliases and hence it works without any issues in your scenario..
0
 
Chaste SwedgeAuthor Commented:
Thanks for the quick response rr, but the order by isn't the issue really. I was sure I would get the outer reference error on the calculated date field in the group by clause. It works. That is what is puzzling me.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I was sure I would get the outer reference error on the calculated date field in the group by clause.

Kindly let me know what you meant by Outer reference..
You had "CONVERT(date,CONVERT(char(10),LoginTime,111))" alone in the GROUP BY clause which was part of the SELECT statement which will work as it is syntactically correct..
0
 
Chaste SwedgeAuthor Commented:
Here is the issue I am speaking of:

Error Message:Msg 164, Level 15, State 1, Line 2
Each GROUP BY expression must contain at least one column that is not an outer reference.

Severity level:15.

Description:This error message appears when you try to use only expressions in a GROUP BY clause that are interpreted as outer references.

Consequences:The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:Error of the Severity level 15 are generated by the user and are corrigible by the user. Every GROUP BY clause must contain at least one column that is not interpreted as outer reference.

Because I am only looking for a calculated field in the select (obviously to get login totals for days), the group by clause does not consider this an 'inner' reference, but rather an outer one.

If you run this example, you will see how this works:

select (3*3) as Nine, COUNT(*)
from UserHist
group by (3*3)


And this is the issue that is confusing me. I cannot understand why this works. I don't have an older instance of SQL available, but I would be willing to bet that my query would not work in SQL 2005 or below. So, did MSFT change something? This is really my question RR. Why does this type of statement that has never worked in the past all of a sudden work properly?
0
 
Chaste SwedgeAuthor Commented:
Ahhh.... That clears it up for me RR. Thank you. I had always assumed it would never work for any contrived/calculated values. So if I declared the date field above as a constant like below, it would fail, correct? (sorry, no time to test right now).

Thanks!

declare @sDate datetime set @sDate=CONVERT(date,CONVERT(char(10),getdate(),111))
select @sDate lDate,COUNT(userID) lCount
FROM UserHist
where LoginTime >= DATEADD(month,-3,getdate())
group by  @sDate
order by lDate
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, above would fail as it is a constant..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.