Solved

Group By Quandry

Posted on 2011-02-25
7
333 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 41
Date conversion in sql server 2012 6 26
SQL JOIN 6 32
Auditing in Azure SQL Database 3 28
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now