473,836 Members | 1,568 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transact-SQL Help - CASE statement and Group By

I've always been mistified why you can't use a column alias in the group by
clause (i.e. you have to re-iterate the entire expression in the group by
clause after having already done it once in the select statement). I'm
mostly a SQL hobbiest, so it's possible that I am not doing this in the most
efficient manner. Anyone care to comment on this with relation to the
following example (is there a way to acheive this without re-stating the
entire CASE statement again in the Group By clause?):

Select 'Age' =
Case
WHEN(SubmittedO n >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedO n <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedO n as varchar(22))
end,
max(SubmittedOn ), COUNT(SCRID) AS NbrSCRs
From SCRView
WHERE
(StatusSort < 90) AND
CustomerID = 8 and
UserID = 133
group by
Case
WHEN(SubmittedO n >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedO n <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedO n as varchar(22))
end
Order by max(submittedon ) desc

Thanks,
Chad
Jul 20 '05 #1
4 28827
you can't use an alias in the where clause either.

its a balance between ease of reading by a human and the ease of processing
by the query plan.

"Chad Richardson" <chad@NIXSPAM_c hadrichardson.c om> wrote in message
news:10******** *****@corp.supe rnews.com...
I've always been mistified why you can't use a column alias in the group
by clause (i.e. you have to re-iterate the entire expression in the group
by clause after having already done it once in the select statement). I'm
mostly a SQL hobbiest, so it's possible that I am not doing this in the
most efficient manner. Anyone care to comment on this with relation to the
following example (is there a way to acheive this without re-stating the
entire CASE statement again in the Group By clause?):

Select 'Age' =
Case
WHEN(SubmittedO n >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedO n <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedO n as varchar(22))
end,
max(SubmittedOn ), COUNT(SCRID) AS NbrSCRs
From SCRView
WHERE
(StatusSort < 90) AND
CustomerID = 8 and
UserID = 133
group by
Case
WHEN(SubmittedO n >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedO n <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedO n as varchar(22))
end
Order by max(submittedon ) desc

Thanks,
Chad

Jul 20 '05 #2
On Wed, 20 Oct 2004 17:45:10 -0600, Chad Richardson wrote:
I've always been mistified why you can't use a column alias in the group by
clause (i.e. you have to re-iterate the entire expression in the group by
clause after having already done it once in the select statement).
Hi Chad,

That's a result of how SQL statements have to be evaluated. The SQL
standard prescribes a specific order of evaluation. (Note that a DBMS may
use another evaluation order for optimization, but only if the end resutls
are not influenced by it - and note that every commercial DBMS does in
fact use this freedom to optimize).

1. First, the FROM clause is evaluated. All tables mentioned in the FROM
clause are joined as specified; the result set (containing all columns
from all tables in the FROM clause) is passed on to the next step. If you
use old-style join syntax (i.e. FROM table1, table2, table3), you'll get a
big and chunky carthesian product in this step.

2. The result set of the FROM clause is then filtered according to the
criteria in the WHERE clause. Rows that don't satisfy the condition are
completely removed from the result set. The remainder of the result set is
passed on to the next step.

3. The result set of the FROM and WHERE clauses is divided into groups, as
specified by the GROUP BY. This step may involve sorting, but it doesn't
have to so you better not rely on it! Note that no aggregation is done at
this point. The result set after this step will therefore violate first
normal form: it has repeating groups (e.g. one age group will hold many
values for SubmittedOn).

4. The grouped result set is then filtered, according to the criteria in
the HAVING clause. If a group doesn't satisfy the conditions, the whole
group is removed from the result set. Note that SQL Server permits one to
use HAVING without GROUP BY (I'm not sure if ANSI standard allows this as
well); in that case, the entire result set is treated as one group and the
HAVING clause will either leave the result set as is or make it an empty
set.

5. Finally, the SELECT clause is evaluated. This is the only step that
will change the columns in the result set (all other steps may include or
remove rows, but leave the collection of columns intact). Columns may be
taken from the intermediate result set (tablename.colu mnname), calculated
from some other columns in the intermediate set and/or some constants; all
columns in the select clause may be given an alias. If the input to the
SELECT step has repeating groups, than these columns may only appear in
aggregate functions.

Officially, the SELECT statement is finished here. The ORDER BY clause is
treated seperately, as this is technically a cursor operation, not a set
operation.

6. The ORDER BY clause is evaluated, using the result set of the SELECT
clause as it's input. The ANSI standard doesn't allow to order by columns
that are not included in the SELECT list, as they are not present in the
input to this step. Columns for the ORDER by should be referred to by the
alias (as the original column name is not present in the input to the
ORDER BY step) or by their ordinal number.
Note: SQL Server does allow to ORDER BY columns not present in the SELECT
list, or even by expressions based on that columns. That can be very
handy, but it can also ne confusing (especially if an alias for a column
in the SELECT list matches the name of a column in one of the tables/views
used in the FROM clause).

As you can see from the above, the GROUP BY is (officially) executed
before the SELECT is executed. Therefore, the result of the CASE
expression in your SELECT is not yet available when the GROUP BY is
carried out.

Anyone care to comment on this with relation to the
following example (is there a way to acheive this without re-stating the
entire CASE statement again in the Group By clause?):


Yes, there is. But you'll have to use a dervide table to achieve it. In
case you're wondering: a derived table means that you insert a subquery in
the FROM clause, at the place where you would normally insert a table or
view name. Derived tables must always be aliased!

Something like this (untested):

Select Age, max(SubmittedOn ), COUNT(SCRID) AS NbrSCRs
From (Select
Case
WHEN(SubmittedO n >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedO n >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedO n <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedO n as varchar(22))
end AS Age, SubmittedOn, SCRID
From SCRView
WHERE
(StatusSort < 90) AND
CustomerID = 8 and
UserID = 133) AS Derived (Age, SubmittedOn, SCRID)
group by Age
Order by 2 desc
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3
Thanks guys for the education. Makes sense now.
Jul 20 '05 #4
-P-
"Chad Richardson" <chad@NIXSPAM_c hadrichardson.c om> wrote in message news:10******** *****@corp.supe rnews.com...
Thanks guys for the education. Makes sense now.

...or, just get a copy of Sybase's SQL Anywhere. It allows you to use aliases in the WHERE clause and the GROUP BY.

www.ianywhere.com

-P-
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5620
by: Demetris | last post by:
Hello there. I have ORACLE 8.1.7.4 server on AIX 5.1. I have another server (BIZTALK) with Oracle client 8.1.7. The system is running ok. I can connect from the client on my database server just fine. However at certain stages we get the error below when it tries to open a connection.Please note that we are loading 150,000 records currently and we open / close a connection for every call. ORA-12154: TNS:could not resolve service name
29
4658
by: Paul | last post by:
Hi, I'd like to limit the number of selections a user can make in a multiple select listbox. I have a note on the interface to say that only x no. of items should be selected and I check the number server side but I'd like to implement some javascript to do the same on the client side. Ideally I'd like the javascript to work in IE5+ and Netscape6+. Thanks,
2
1880
by: pierig.gueguen | last post by:
Hello, I encounter a problem with a small portion of sqlcode. I try to go on database using "use dbname" but i always stay in master. I execute script with the sa user. declare @dbname sysname declare @ret_code int DECLARE db_cursor CURSOR FOR
0
1509
by: perspolis | last post by:
Hi all I used SqlTransaction inmy application.. SqlTransaction transact=sqlConnection1.BeginTransaction(IsoLationLevel.Something); sqlSelect.Transaction=transact; sqlInsert.Transaction=transact; ,............... but any of IsolationLevel stat I use it dosen't work properly and it works as Serializable isolation level.. When I'm using a transaction for a master-slave tables and I want when
3
1108
by: Tina | last post by:
I have been trying to prepare for 70-305 using the Preplogic CD that came with the QUE book by Mike Gunderloy. This test has wrong answers. For instance... PrepLogic question #39 asks what file are TraceSwitch setting changed in. Their answer is applicationname.config instead of Web.config. Its the wrong answer! In another question it asks how to stream a dataset named TransactDS to XML their answer is...
4
12289
by: bbdobuddy | last post by:
Hi, How do I open a Microsoft Access 2003 form from Visual Basic.net Thanks in advance bbdobuddy
0
1431
by: et_ck | last post by:
Hi, We have a web services running to facilitate our clients in performing transactions. Below is the sample message flow: 1) App -> WS WS - perform transact WS - send response 2)
11
14676
by: moondaddy | last post by:
I have a .net 2.0 smarclient app and am trying to deploy it to IIS where users can access it from. I created an application folder in IIS where I'm trying to deployee to. 1) When the application folder was set to 'Allow scripts only', I get an error: Failed to connect to 'http://192.168.0.5/TransAct/' with the following error:
23
5274
by: Ciara9 | last post by:
I have a table of static values that I am acessing from another table to populate combo boxes. Next I have a form where this information is selected by the user and updates directly to the table. Everything works fine. I can create simple queries and reports with no problem as well as update the data. If I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo...
3
1752
by: Scott Bradley | last post by:
Hi All, We are using SQL2000 servers with transact replication to a warm stand-by server. We are seeing the following error from the Log reader agents. Does anyone have any insight on this error? The process could not execute 'sp_MSadd_repl_commands27hp' TIA Scott B.
0
10852
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10553
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10596
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9382
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7793
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.