Geek Answers Handbook

MySQL: using aliases in aggregate functions

I have an SQL statement like this:

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

But MySQL does not allow this because foo is an alias. Does anyone have an idea of ​​how this can be done in SQL?

+4
source share
4 answers

No, you cannot use an alias in a select or WHERE clause. You can use only an alias in GROUP BY, HAVING or ORDER BY.

You can also use the aliases defined in the subquery:

 SELECT foo, SUM(foo) AS foo_sum FROM ( SELECT COUNT(*) AS foo FROM bar );
+5
source 
 SELECT SUM(foo) as foo_sum FROM ( SELECT COUNT(*) AS foo FROM bar GROUP BY baz )
+1
source

I think this is not a good idea. If you want to make a big request, it is better to do it without a subquery. Use COUNT(*) and larger functions without an alias if you need it.

I made a request with aliases and subqueries. It took about an hour! Then I replayed the request without an alias. This was reduced to about 45 minutes. Forget about subqueries in this situation. This is less complicated and prettier, but makes your request slower.

0
source

I don’t know what you are trying to do, but using the above solutions you use a subquery in the alias table, which is inefficient.

 SELECT foo FROM (SELECT COUNT(*) AS foo FROM employees) AS T;

Basically, T is your alias table, and it returns the count column foo, which is a separate record, and it makes no sense to use the SUM (foo) function on it, since it is a single record.

Anyway, the simple answer is:

 SELECT Count(1) AS foo from employees;

Since the COUNT function will return the same results no matter which NOT NULL fields you include as parameters for the COUNT function (that is: in parentheses), you can use COUNT (1) to improve performance. Now the database engine will not need to retrieve any data fields; instead, it will just get an integer value of 1.

0
source

More articles:


All Articles