MySQL: Use Aliased Fields in Aggregate Functions

Question

I have a SQL statement similar to this:

SELECT COUNT(*) AS foo, SUM(foo) AS foo_sum FROM bar

But MySQL doesn't allow this because foo is an alias. Does anyone have an idea of how this could be accomplished in SQL?

Answer 1

No, you can't use an alias in the select-list or a WHERE clause. You can only use the alias in a GROUP BY, HAVING, or ORDER BY.

You can also use aliases defined in a subquery:

SELECT foo, SUM(foo) AS foo_sum
FROM (
  SELECT COUNT(*) AS foo
  FROM bar
);