Sirs, good afternoon. I'm missing a way to name field in SQL queries.
For instance, say I wanted to write

SELECT
function() AS field
GROUP BY
field

I couldn't do that. I'd have to write

SELECT
function() AS field
GROUP BY
function()

Is there a way to name this field so that I don't have to write the
function call twice?

Daniel Bastos:

Good night, Daniel.
[No, this ain't going to become a time zone thread]


You might want to wrap things in a subquery:

SELECT myField FROM
(SELECT function() AS field) AS t
GROUP BY myField

[not tested, of course]


I'm just curious what the semantics of 'function' might be, as I'm not
sure how this makes sense. Could you post the real query, please?

On Wed, 21 Dec 2011 12:15:24 -0800 (PST), Daniel Bastos wrote:
Essentially, the column just receives the alias (the "AS name") after
the GROUP BY gets processed. It's the same reason you couldn't use
that alias `field` in a WHERE condition. The WHERE issue has a
workaround in the HAVING clause. Similarly, the GROUP BY (and ORDER BY,
and a few other such clauses) have a workaround of being able to use the
column index instead of the name, like

SELECT
function() AS field
GROUP BY
1 ;

Or, you can force some of the mess into a subquery and get names and
things resolved before handing it to the group, as mentioned in the
other post.

--
64. I will see a competent psychiatrist and get cured of all extremely
unusual phobias and bizarre compulsive habits which could prove to
be a disadvantage.
--Peter Anspach's list of things to do as an Evil Overlord

In article <slrnjf4jeb.51n.hellsop (AT) nibelheim (DOT) ninehells.com>,
Peter H. Coffin <hellsop (AT) ninehells (DOT) com> wrote:
You should be able to, in MySQL.

This is necessary in MS-SQL Server (in 2000, at least), but not in MySQL.

The first way you tried should be fine. If it failed, perhaps there
was a different reason.

Actually, the column receives the alias after the WHERE is processed,
but before records are grouped and ordered, so you CAN use an alias
in a GROUP BY. I have just verified this on MySQL 4.1.15 and 5.0.77.
I would be surprised if later versions have removed this flexibility.

E.g. on a table called colours, having a column named colour:

SELECT LEFT(colour,3) AS colprefix, COUNT(*)
FROM colours
GROUP BY colprefix;

This worked fine.

Also, HAVING is not a "workaround"; it is a filter that is applied
to the result set AFTER the records are grouped and ordered, whereas
WHERE is applied before, to define the set of records that will be
grouped and ordered.

Cheers
Tony

--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

Peter H. Coffin:


Really?
From the manual:

=========
A select_expr can be given an alias using AS alias_name. The alias is
used as the expression's column name and can be used in *GROUP* *BY*,
ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;
=========
http://dev.mysql.com/doc/refman/5.1/en/select.html




I just wonder what the OP wants, given that there is no FROM clause in
his query.

Something like
SELECT rand(1) as randomfield GROUP BY ...
will fail, regardless whether written as
SELECT rand(1) as randomfield GROUP BY randomfield
or
SELECT rand(1) as randomfield GROUP BY rand(1)

The general "synatx error" message, with "...near GROUP BY" is issued.