Issue with GROUP BY and alias

[bletera]

If I use a computed field with any function and alias in SELECT, and use this alias in a GROUP BY clause, this query evaluates with an error that there is no field with that name (alias).

Example SELECT SUBSTR(c3, INSTR(c3, "#") + 1) AS Vare, COUNT(Vare) AS Count FROM C:\Program Files (x86)\server\Logs\status.log GROUP BY Vare;

Error: query.sql [L:7 C:2] field Vare does not exist

[mithrandie]

SELECT query is processed in this order.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Therefore, column aliases specified in the SELECT clause cannot be used in clauses processed before it.

[bletera]

Thank you for your reply. Then how to I do a SELECT for a file that does not have a field name and I need to group a field by a calculated field? Is there a solution?

[mithrandie]

The easiest way is to use a subquery.

SELECT t.Vare,
       COUNT(t.Vare) AS Count
  FROM (
         SELECT SUBSTR(c3, INSTR(c3, "#") + 1) AS Vare
           FROM `status.log`
       ) t
 GROUP BY t.Vare

[bletera]

When I try your query, get this error syntax error: unexpected token "Count".

When run standalone inner query, all it`s OK, but with whole query, I get error.

[mithrandie]

Sorry, count is a reserved word and must be enclosed.

SELECT t.Vare,
       COUNT(t.Vare) AS `Count`
  FROM (
         SELECT SUBSTR(c3, INSTR(c3, "#") + 1) AS Vare
           FROM `status.log`
       ) t
 GROUP BY t.Vare

[bletera]

Thank you. Query is working.