SQL: Using alias of a column in a function in the same select statement

Question

I am just trying to simplify a Select statement:

Select (a+b+c)/3 AS V, MIN((a+b+c)/3) as Min, MAX((a+b+c)/3) as Max from ....

Is there a clean way to avoid repeating that formula (a+b+c)/3 in the aggregate functions?

Answer 1

SELECT 
  Result/3 AS V, 
  MIN(Result/3) as Min, 
  MAX(Result/3) as Max
FROM
(
  SELECT (a+b+c) AS Result from Table
) AS outerTable

OR even

SELECT 
  Result AS V, 
  MIN(Result) as Min, 
  MAX(Result) as Max
FROM
(
  SELECT (a+b+c)/3 AS Result from Table
) AS outerTable

Answer 2

If you need the value (a+b+c)/3 in more than just one query and your SQL dialect supports views, you might want to consider using a View.

The following code can be used in MySQL to create a View that contains all columns of the table MY_TABLE_NAME + an additional column that contains the value (a+b+c)/3.

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_FULL_VIEW` AS
SELECT MY_TABLE_NAME.*, (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

The following code can be used in MySQL to create a View that contains only the columns a, b and c + an additional column that contains the value (a+b+c)/3.

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_LIMITED_VIEW` AS
SELECT a, b, c, (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

The following code can be used in MySQL to create a View in MySQL that contains only the value (a+b+c)/3.

CREATE ALGORITHM = UNDEFINED
SQL SECURITY DEFINER VIEW  `MY_TABLE_NAME_AVERAGE_VIEW` AS
SELECT (a+b+c)/3 AS Average
   FROM  `MY_TABLE_NAME`;

After you created your view, use the view instead of the table in your select queries.

---

Assuming you created view MY_TABLE_NAME_AVERAGE_VIEW, you could replace your SELECT statement with this :

SELECT Average AS V, MIN(Average) AS Min, MAX(Average) AS Max
FROM MY_TABLE_NAME_AVERAGE_VIEW;

Assuming you created view MY_TABLE_NAME_LIMITED_VIEW, and you also need to include values a, b and C, you could replace your SELECT statement with this :

SELECT a, b, c, Average AS V, MIN(Average) AS Min, MAX(Average) AS Max
FROM MY_TABLE_NAME_LIMITED_VIEW;