Transact-SQL Help - CASE statement and Group By

[Chad Richardson]

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(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedOn 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(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedOn as varchar(22))
end
Order by max(submittedon) desc

Thanks,
Chad

[David Rawheiser]

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_chadrichardson.com> wrote in message
news:10*************@corp.supernews.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(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedOn 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(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedOn as varchar(22))
end
Order by max(submittedon) desc

Thanks,
Chad

[Hugo Kornelis]

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.columnname), 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(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'
WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=
DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'
WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'
ELSE cast(SubmittedOn 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)

[Chad Richardson]

Thanks guys for the education. Makes sense now.

[-P-]

"Chad Richardson" <chad@NIXSPAM_chadrichardson.com> wrote in message news:10*************@corp.supernews.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-