Solved

Group By Quandry

Posted on 2011-02-25
7
351 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 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: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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

751 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