MySQL grouping by a previously declared alias, what do I wrap it in? ' OR `

Question

I have an SQL query that has an alias in the SELECT statement

SELECT CONCAT(YEAR(r.Date),_utf8'-',_utf8'Q',QUARTER(r.Date)) AS 'QuarterYear'

Later, I want to refer to this in my group by statement.

I'm a little confused...should I wrap this with backticks, single quote or just leave it unwrapped int he group by

GROUP BY `QuarterYear `

or should I do this?: GROUP BY 'QuarterYear'

or just this?: GROUP BY QuarterYear

Answer 1

You can escape aliases or column names using a backtick but it's only required when your column has a reserved word as its name (i.e. timestamp), so you don't need to. Avoid using reserved words instead, so you'll never need to escape column names with backticks.

You should never use single quotes to wrap column names, this is only for data.

EDIT: You can't group by aliases of group columns (i.e. MIN(), MAX(), COUNT() etc) but using aliases of functions (i.e. CONCAT()) you can.

Answer 2

Unfortunatelly, you can not use the alias because the alias is not yet available in GROUP BY. You can use the number of the column though:

SELECT CONCAT(YEAR(r.Date),_utf8'-',_utf8'Q',QUARTER(r.Date)) AS 'QuarterYear'
GROUP BY 1;

In this particular case you can just use DISTINCT modifier:

SELECT DISTINCT CONCAT(YEAR(r.Date),_utf8'-',_utf8'Q',QUARTER(r.Date)) AS 'QuarterYear'