?
Solved

Group By Quandry

Posted on 2011-02-25
7
Medium Priority
?
366 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:Chaste Swedge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Chaste Swedge
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Chaste Swedge
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 2000 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:Chaste Swedge
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

718 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