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

SELECT SUM(foo) as foo_sum
FROM 
(
    SELECT COUNT(*) AS foo
    FROM bar
    GROUP BY baz
)

Answer 2

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
);

Answer 3

It would be interesting to conjecture what the optimizer might try to do with taking the sum of the count of the rows at the same level of aggregation, and then using it to filter individual rows ...

Answer 4

I think it's not a good action. If u want to make big query, better if u will do it without subquery. Use COUNT(*) and bigger functions without alias, if u need it. I made query with aliases and subqueries. Man, i'd been waiting for something about hour! Then I reproduced query without alias. Wait time was smth about 45 minutes. Forget about subqueries in this situation. It's less hard and more pretty, but it makes your query slowly.