Solved

Group By Quandry

Posted on 2011-02-25
7
346 Views
Last Modified: 2012-06-22
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!
0
Comment
Question by:Jim Hoell
  • 4
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34979975
ORDER BY Clause will support column aliases and hence it works without any issues in your scenario..
0
 

Author Comment

by:Jim Hoell
ID: 34980079
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34980177
>> 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Jim Hoell
ID: 34980553
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34981276
>> 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
 

Author Comment

by:Jim Hoell
ID: 34981694
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34981779
Yes, above would fail as it is a constant..
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question