StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
951855
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
4
CommunityOwnedDate
2009-06-04T20:08:06.553
CreationDate
2009-06-04T17:06:00.260
FavoriteCount
0
LastActivityDate
2009-06-09T14:39:08.530
LastEditDate
2009-06-09T14:39:08.530
LastEditorUserId
107744
OwnerUserId
107744
ParentId
951836
PostTypeId
2
Score
9
ViewCount
0
LastEditorDisplayName
text
Body
Q: how to get an alias to use in the GROUP BY clause One approach is to use an inline view. [EDIT] The answer from Remus Rusanu (+1!) gives an example of a Common Table Expression to accomplish the same thing. [/EDIT] The inline view gets you a simple "alias" for the complex expression which you can then reference in a GROUP BY clause in an outer query: <pre><code>select count(d.callid) , d.duration from (select callid , case when callDuration >= 600 then 12 when callDuration >= 540 then 11 when callDuration >= 480 then 10 when callDuration >= 420 then 9 when callDuration >= 360 then 8 when callDuration >= 300 then 7 when callDuration >= 240 then 6 when callDuration >= 180 then 5 when callDuration >= 120 then 4 when callDuration >= 60 then 3 when callDuration >= 30 then 2 when callDuration > 0 then 1 --else null end as duration from callmetatbl where programid = 1001 and callDuration > 0 ) d group by d.duration </code></pre> Let's unpack that. <ul> <li>the inner (indented) query is called and inline view (we given it an alias <code>d</code>)</li> <li>in the outer query, we can reference the alias <code>duration</code> from <code>d</code></li> </ul> That should be sufficient to answer your question. If you're looking for an equivalent replacement expression, the one from tekBlues (+1 !) is the right answer (it works on the boundary and for non-integers.) With the replacement expression from tekBlues (+1!): <pre><code>select count(d.callid) , d.duration from (select callid , case when callduration >=30 and callduration<600 then floor(callduration/60)+2 when callduration>0 and callduration< 30 then 1 when callduration>=600 then 12 end as duration from callmetatbl where programid = 1001 and callDuration > 0 ) d group by d.duration </code></pre> (This should be sufficient to answer your question.) <hr> [UPDATE:] sample user defined function (a replacement for inline CASE expression) <pre><code>CREATE FUNCTION [dev].[udf_duration](@cd FLOAT) RETURNS SMALLINT AS BEGIN DECLARE @bucket SMALLINT SET @bucket = CASE WHEN @cd >= 600 THEN 12 WHEN @cd >= 540 THEN 11 WHEN @cd >= 480 THEN 10 WHEN @cd >= 420 THEN 9 WHEN @cd >= 360 THEN 8 WHEN @cd >= 300 THEN 7 WHEN @cd >= 240 THEN 6 WHEN @cd >= 180 THEN 5 WHEN @cd >= 120 THEN 4 WHEN @cd >= 60 THEN 3 WHEN @cd >= 30 THEN 2 WHEN @cd > 0 THEN 1 --ELSE NULL END RETURN @bucket END select count(callid) , [dev].[udf_duration](callDuration) from callmetatbl where programid = 1001 and callDuration > 0 group by [dev].[udf_duration](callDuration) </code></pre> NOTES: be aware that the user defined function will add overhead, and (of course) add a dependency on another database object. This example function is equivalent to the original expression. The OP CASE expression doesn't have any gaps, but it does reference each "breakpoint" twice, I prefer to test only the lower bound. (CASE returns when a condition is satisfied. Doing the tests in reverse lets the unhandled case (<=0 or NULL) fall through without test, an <code>ELSE NULL</code> is not necessary, but could be added for completeness. ADDITIONAL DETAILS (Be sure to check the performance and the optimizer plan, to make sure it's the same as (or not significantly worse than) the original. In the past, I've had problems getting predicates pushed into the inline view, doesn't look like it will be a problem in your case.) stored view Note that the inline view could also be stored as view definition in the database. But there's no reason to do that, other than to "hide" the complex expression from your statement. simplifying the complex expression Another way to make a complex expression "simpler" is to use a user defined function. But a user defined function comes with its own set of issues (including degraded performance.) add database "lookup" table Some answers recommend adding a "lookup" table to the database. I don't see that this is really necessary. It could be done of course, and could make sense if you want to be able to derive different values for <code>duration</code> from <code>callDuration</code>, on the fly, without having to modify your query and without having to run any DDL statements (e.g. to alter a view definition, or modify a user defined function). With a join to a "lookup" table, one benefit is that you could make the query return different result sets by just performing DML operations on the "lookup" table. But that same advantage may actually be a drawback as well. Consider carefully if the benefit actually outweighs the downside. Consider the impact that new table will have on unit testing, how to verify the contents of the lookup table are valid and not changed (any overlaps? any gaps?), impact on ongoing maintenance to the code (due to the additional complexity). some BIG assumptions A lot of the answers given here seem to assume that <code>callDuration</code> is an INTEGER datatype. It seems they have overlooked the possibility that it's not an integer, but maybe I missed that nugget in the question. It's fairly simple test case to demonstrate that: <pre><code>callDuration BETWEEN 0 AND 30 </code></pre> is NOT equivalent to <pre><code>callDuration > 0 AND callDuration < 30 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSimplifying (aliasing) T-SQL CASE statements. Any improvement possible?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USspencer7593
UserOwnerUserId
1. USspencer7593
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POSimplifying (aliasing) T-SQL CASE statements. Any improvement possible?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.