...the trick is, NOT to repeat the column alias in the GROUP BY clause.

The Critter's

\nSELECT\n  mycalc = TO_CHAR(acolumn),\n  count(*)\nFROM mytable\nGROUP BY\n  TO_CHAR(acolumn)\n

is (AFAICS?) equivalent to:

\nSELECT\n  TO_CHAR(acolumn) mycalc,\n  count(*) freq\nFROM mytable\nGROUP BY\n  TO_CHAR(acolumn)\n

, which is equivalent to:

\nSELECT\n  acolumnwithsomedamnlongstupidname myshortname,\n  count(*) freq\nFROM mytable\nGROUP BY\n  acolumnwithsomedamnlongstupidname\n

-- this works, whereas

\nSELECT\n  acolumnwithsomedamnlongstupidname myshortname,\n  count(*) freq\nFROM mytable\nGROUP BY\n  acolumnwithsomedamnlongstupidname myshortname\n

or

\nSELECT\n  acolumnwithsomedamnlongstupidname myshortname,\n  count(*) freq\nFROM mytable\nGROUP BY\n  myshortname\n

don't work, IIRC.

So, you could say that what makes it work or not, is that in the GROUP BY clause, you have to have "the original form" of what you are selecting -- whether that be "somedamnlongstupidcolumnname" or a calculation -- but NOT the alias you've specified for it in the SELECT clause.

(Though I must admit I'm not sure if this really holds for Critter's result_or_alias = calculation[_or_originalname?] form; I always use the originalname_or_calculation alias style, myself. But I think it's rather probable that this works the exact same way, because I'm guessing it is the exact same thing, only written differently.)

HTH!

At least in this context: Bryce's problem was -- Chris thought, and I'll bet -- that *what he selected* in the first part of the select statement, the "column list" clause, he later -- in the Group By clause -- referred to by the alias he had defined for it, when he should have used "the original form". And it doesn't matter *what* exactly "what he selected" *is*; it can be a simple native column (aliased just for brevity/clarity), *or* a complex calculation involving gazillions of columns and googleplexes of function calls... To Oracle, it's all the same, as long as it *knows* it's the same thing you mean. (And it knows this if-and-only-if you refer to it in the same way you first *defined*, not alias-named, it.)

"It's the same"... As in, Oracle treats the result of the calculation exactly *as if it were* a column; to all intents and purposes, a "virtual column" -- that Oracle *does* know how to group on, original column or no -- has been born from his calculation. At least that's the smartest way for Bryce to think about it, because that allows him to see the obvious parallel with the simpler (this *is* for *Bryce's* benefit, after all!) case where there *is* an original column, that he is just renaming with an alias.

Now don't go protesting my paedagogical tricks -- at least, not until you recognise them for what they are -- any more, ya bastidge! :-)


[Edit:] And BTW, another way to put this -- i.e, all of the above -- would be, "What part of whether that be 'somedamnlongstupidcolumnname' or a calculation didn't you understand, DrooK?"

...language (or IDE environment) on the client.

In Ben's case, Perl (naturally!); in Bryce's, [ObCheapShot:] either "L" or "TOP", of course! :-)