SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Manie Verster / Group by / Latest Posts

RE: Group by

timothy bates — Tue, 02 Nov 2010 12:58:08 GMT

Based on Hugo's LOGICAL progression where the group by preceeds the select. The alias is not yet defined so grouping is not possible against a column that does not yet exist. The Order By occurs post Select where the alias has been defined so it is acceptable. An alternative solution is Group by datepart(yyyy,orderdate).

RE: Group by

VinceV — Thu, 30 Sep 2010 07:54:15 GMT

Great question and even better follow up discussions. Thanks!

RE: Group by

Paul White — Thu, 16 Sep 2010 05:22:26 GMT

There is a "workaround":[code="sql"]SELECT DV.[Year], SOH.SalesPersonID, AverageOrderAmt = SUM(SOH.TotalDue)FROM Sales.SalesOrderHeader SOHCROSSAPPLY (SELECT DATEPART(yyyy,OrderDate)) DV ([Year])GROUP BY DV.[Year], SOH.SalesPersonIDORDER BY DV.[Year] ;[/code]Over to you, Hugo.Paul

RE: Group by

Daniel Bowlin — Tue, 14 Sep 2010 07:54:47 GMT

I was up late last night working. I am looking at the question and thinking, "Gee, I didn't think you could group that way" and in my fatigue I chose the wrong answer instead of realizing that you [b]can't [/b]group that way.

RE: Group by

L' Eomot Inversé — Fri, 10 Sep 2010 19:28:18 GMT

[quote][b]Manie Verster (9/10/2010)[/b][hr]Tom please tell me what this means. "Na tog mi gun tuit mi ach ma thuiteas tog! Thig crìoch air an t-saoghal ach mairidh gaol is ceòl "[/quote]As Hugo said, it's Scotland's Celtic language, usually in English called Scottish Gaelic, or Gaelic for short (which of course creates confusion with Manx and Irish Gaelics) or in itself Gàidhlig na h-Alba. The first line means literally "Don't lift me unless I fall but if I fall do lift" but really "don't correct me unless I'm wrong but if I'm wrong do". The second line is a very old saying: "An end will come upon the world but love and music will survive".

RE: Group by

Hugo Kornelis — Fri, 10 Sep 2010 00:49:54 GMT

[quote][b]Manie Verster (9/10/2010)[/b][hr]Tom please tell me what this means. "Na tog mi gun tuit mi ach ma thuiteas tog! Thig crìoch air an t-saoghal ach mairidh gaol is ceòl "[/quote]Google translate recognises it as Scottish Celtic, but is not yet able to translate that to either English or Dutch.Bing Translator thinks it's Hungarian and translates it to English as "Na train what gun tuit what ach thuiteas train today! Thig crìoch air an t-saoghal ach mairidh gaol also ceòl".Guess which of the two competing sites I believe ;-)

RE: Group by

Manie Verster — Fri, 10 Sep 2010 00:39:33 GMT

Thanks to all for the positive comments and thanks Hugo for the logical explanation you gave. Helps a lot!Tom please tell me what this means. "Na tog mi gun tuit mi ach ma thuiteas tog! Thig crìoch air an t-saoghal ach mairidh gaol is ceòl "

RE: Group by

L' Eomot Inversé — Tue, 07 Sep 2010 10:30:35 GMT

Nice question. And nice description of logical order from Hugo.

RE: Group by

webrunner — Tue, 07 Sep 2010 07:55:36 GMT

Good question, thanks. I have caused this error many times while composing my queries, so when I realized there was an alias in the GROUP BY clause, I figured that had to be the answer.- webrunner(Edit) P.S. Thanks, Hugo, for the detailed explanation of why this error happens.

RE: Group by

SQLRNNR — Mon, 06 Sep 2010 09:35:34 GMT

Thanks for the question

RE: Group by

cengland0 — Mon, 06 Sep 2010 05:55:18 GMT

Nice question, I got it right. I did want to point out that since the correct answer is that it will generate an error, no rows will be displayed because of that error. it could then be argued that the "No rows will be displayed" answer could be considered a correct choice as well.

RE: Group by

tommyh — Mon, 06 Sep 2010 04:52:35 GMT

[quote][b]amresh_jhingan (9/6/2010)[/b][hr]I parsed the query before running it.. and it gave me "Command(s) completed successfully.". Not sure why.. ?[/quote]Syntax check only. And the syntax is okay. But that doesnt mean that it will run. You can for instance parse say "select something from nothing" without having a table called "nothing".

RE: Group by

AmarJhingan — Mon, 06 Sep 2010 04:42:22 GMT

I parsed the query before running it.. and it gave me "Command(s) completed successfully.". Not sure why.. ?

RE: Group by

Koen Verbeeck — Mon, 06 Sep 2010 04:05:38 GMT

[quote][b]tommyh (9/6/2010)[/b][hr][quote][b]da-zero (9/6/2010)[/b][hr]And who is [i]we[/i]?[/quote]Hrmm having a hard time deciding if that is supposed to be sarcastic or not. But anyway.I used "we" to represent everyone using SQL. Honestly dont know how else i could have written that.[/quote]Whoops. My bad.I didn't see you were talking about table aliases instead of column aliases. So I thought you were referring to some database (other than SQL Server) where column aliases in the group by are possible.Guess I'm not fully awake yet...

RE: Group by

Hugo Kornelis — Mon, 06 Sep 2010 03:37:50 GMT

[quote][b]Open Minded (9/6/2010)[/b][hr]I got the answer right, after a long series of wrongs.[quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!1. FROM clause. Build an intermediate set as a copy of the single table in the FROM clause, or as the result of joining the multiple tables. Or supply a single-row result set with no columns if no FROM is specified.[/quote]Whoa!! Hugo, I always thought this behavior was from Visual Foxpro, to copy the entire table first before selecting the data.I always thought in SQL, the data was Selected first before Where and Group, etc. Is[/quote]Just remember, the description I gave is for the [i]logical[/i] processing order. Actual processing order is always different - the optimizer picks the "best" plan to get the results. Making a competely redundant copy of a table will never be included in that plan!

RE: Group by

Open Minded — Mon, 06 Sep 2010 03:30:19 GMT

I got the answer right, after a long series of wrongs.[quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!1. FROM clause. Build an intermediate set as a copy of the single table in the FROM clause, or as the result of joining the multiple tables. Or supply a single-row result set with no columns if no FROM is specified.[/quote]Whoa!! Hugo, I always thought this behavior was from Visual Foxpro, to copy the entire table first before selecting the data.I always thought in SQL, the data was Selected first before Where and Group, etc. Is

RE: Group by

tommyh — Mon, 06 Sep 2010 03:12:45 GMT

[quote][b]Hugo Kornelis (9/6/2010)[/b][hr][quote][b]tommyh (9/6/2010)[/b][hr][quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:[/quote]Because Microsoft havent implemented it.[/quote]No, because Microsoft has chosen not to go against the standard.[/quote]I think we see "implemented" a bit differently. I see it as something not being in the software (regardless of reason). And as for their reason. Well maybe it was because they didnt want to go against the standard, maybe not. Microsoft aint that know to obay standards.

RE: Group by

Hugo Kornelis — Mon, 06 Sep 2010 03:00:20 GMT

[quote][b]tommyh (9/6/2010)[/b][hr][quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:[/quote]Because Microsoft havent implemented it.[/quote]No, because Microsoft has chosen not to go against the standard.From ISO/IEC 9075-2:2003 (E), 7.9 <group by clause>:"Syntax Rules1) Each <grouping column reference> shall unambiguously reference a column of the table resulting fromthe <from clause>. A column referenced in a <group by clause> is a grouping column."SQL Server does allow you to use expressions in the GROUP BY instead of only simple column references (which extends the standard but oes not violate it). Allowing column aliases would be a violation of the standard, and would break compatibility with ANSI-standard conformant products and code.[quote]We are allowed to use aliases in Group by (and other stuff) in the form of table aliases. Therefore there is no real reason for not allowing column aliases as well (well maybe a bit of performance).[/quote]Of course we can use a table alias in the GROUP BY. That follows logically from the same logical query evaluation order described above. The FROM clause is evaluated first. Table aliases are assigned in the FROM clause. As a result, you can use them in alll other clauses (and, in fact, MUST use them - once you assign a table alias, you can no longer reference the table by its original table name).

RE: Group by

tommyh — Mon, 06 Sep 2010 02:25:33 GMT

[quote][b]da-zero (9/6/2010)[/b][hr][quote][b]tommyh (9/6/2010)[/b][hr][quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:[/quote]Because Microsoft havent implemented it.We are allowed to use aliases in Group by (and other stuff) in the form of table aliases. Therefore there is no real reason for not allowing column aliases as well (well maybe a bit of performance).[/quote]And who is [i]we[/i]?[/quote]Hrmm having a hard time deciding if that is supposed to be sarcastic or not. But anyway.I used "we" to represent everyone using SQL. Honestly dont know how else i could have written that.

RE: Group by

Koen Verbeeck — Mon, 06 Sep 2010 02:06:45 GMT

RE: Group by

Stewart "Arturius" Campbell — Mon, 06 Sep 2010 01:51:12 GMT

Good question.Also contributes to the clarification of the differences between GROUP BY and ORDER BY (I have had lengthy discussions with numerous front-end developers, over the question "why is my result set not ordered? I have a group by in my select")

RE: Group by

tommyh — Mon, 06 Sep 2010 01:49:58 GMT

[quote][b]Hugo Kornelis (9/6/2010)[/b][hr]Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:[/quote]Because Microsoft havent implemented it.We are allowed to use aliases in Group by (and other stuff) in the form of table aliases. Therefore there is no real reason for not allowing column aliases as well (well maybe a bit of performance).

RE: Group by

SamaReddy — Mon, 06 Sep 2010 01:41:10 GMT

Thanks for the question!

RE: Group by

Hugo Kornelis — Mon, 06 Sep 2010 01:00:11 GMT

Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:The correct result of any SQL query is defined in terms of the logical(*) processing order.1. FROM clause. Build an intermediate set as a copy of the single table in the FROM clause, or as the result of joining the multiple tables. Or supply a single-row result set with no columns if no FROM is specified.2. WHERE clause. For each row in the output of step 1, evaluate the WHERE condition. If the result is True, add the row to the intermediate result set that is the output of this step; if it evaluates to False or Unknown (remember three-valued logic!!), discard the row. If no WHERE clause is specified, all rows are assumed to pass the test.3. GROUP BY clause. Create a new intermediate result set by taking the output of step 2 and forming groups based on the columns or expressions in the GROUP BY expression. If no GROUP BY is present, but the query does include a HAVING clause and/or does use aggregate functions in the SELECT clause, all rows are combined into one single group (i.e., "GROUP BY ()" is assumed). Otherwise, no grouping is applied (all rows are retained as individual rows).4. HAVING clause. Similar to the WHERE clause, the expression in the HAVING clause is evaluated - but not for individual rows, but for each group in the output of step 3. If the condition evaluates to True, the group is inserted into the intermediate result set of this step; if it's False or Unknown, the entire group gets discarded. 5. SELECT clause. Based on the remaining rows and groups in the output of step 4, the result set of the query is constructed. Each expression in the SELECT list becomes a column in the result set. Columns in the intermediate result set that are not included in the SELECT list are dropped (unless SELECT * is used, becuase that is a shorthand for "all columns in the intermediate result set").6. ORDER BY clause. Officially a non-relational operation, since relational sets are unordered by definition. But as a service for the front-end, the database can convert the result set ("set" - meaning unordered) to a result cursor ("cursor" - ordered collection). Officially, [i]only[/i] columns or expressions that are in the SELECT clause can be used in the ORDER BY clause (as the other columns were dropped from the result, and hence not included in the input of this step). SQL Server does allow this. Logically, you can consider SQL Server adding some extra colums to the SELECT list, with an "invisible" attribute.Note the absence of subqueries in this list. Logically(*), whenever a subqueriy is encountered in the steps above, evaluation of the outer query is halted, the evaluation process for the subquery is evaluated (steps 1 through 5, as ORDER BY is not allowed in a subquery [for simplicity sake, I choose to not include the SQL Server dialect TOP ... ORDER BY in this description]), and when the result of the subquery is known, the processing of the outer query is resumed.(*) Note the term "logical". There is no requirement at all that queries are actually executed in this order; as long as the results match those that would be returned by following this order. In fact, one of the key components of SQL Server (and any RDBMS) is the optimizer, a component that considers many strategies to find the desired results and picks the one likely to be the "cheapest" in terms of execution time and resource usage.

RE: Group by

manoj.kumar-1078069 — Sun, 05 Sep 2010 21:20:32 GMT

Got it correct...:-)

RE: Group by

UMG Developer — Sun, 05 Sep 2010 17:15:26 GMT

Thanks for the question!I knew the correct answer, but picked the wrong one.. Argh. (I guess I need to pay more attention when I am multi-tasking.)

Group by

Manie Verster — Sun, 05 Sep 2010 01:49:43 GMT

Comments posted to this topic are about the item [B]Group by[/B]