Sum columns in mysql then use the result in where clause

Question

I want to be able to do this:

SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal 
FROM dept 
     INNER JOIN invoices ON invoices.id_dept = dept.id 
WHERE sumTotal > 10000

But I am getting an unknown column on using "sumTotal".

Is this possible?

Answer 1

Use HAVING:

SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal 
FROM dept 
INNER JOIN invoices
    ON invoices.id_dept = dept.id 
HAVING sumTotal > 10000

The problem is that the WHERE clause is executed before the SELECT statement. Therefore the sumTotal column is not yet available.

The HAVING clause is executed after the SELECT statement. It kinds of filter the results out after you have selected everything. Bear in mind, though, because of that using HAVING is slower. It operates on the whole set of rows.

From the MySQL documentation:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

---

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

SELECT user, MAX(salary)
FROM users
GROUP BY user
HAVING MAX(salary) > 10;

Do not use HAVING for items that should be in the WHERE clause.

Answer 2

Or use WHERE...

SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal 
  FROM dept 
  JOIN invoices ON invoices.id_dept = dept.id 
 WHERE invoices.col1 + invoices.col2 + invoices.col3 > 10000