select sum where sum

Question

Hi there,

I want to select some entries based on a max+sum condition.

mytable
----------
id | col1 | col2

I want to select all entries that have the sum of col1 & col2 greater than or equal to the max of sum minus X. (don't ask me why :) )

So far I managed to get the sum OK (hereafter aliased as "total") with:

SELECT id,SUM(col1 + col2) AS total FROM mytable GROUP BY id;

I also managed to get the MAX of the sum OK (with a ORDER BY/LIMIT workaround though):

SELECT id,SUM(col + col) as total FROM mytable GROUP BY id ORDER BY total DESC LIMIT 1;

However everytime I try to re-use my alias as a condition (e.g. WHERE total >= ...) I get an "Unknown column" error

Anything would be greatly appreciated

Answer 1

If you are using group by, you'll need to use a having clause:

SELECT id,SUM(col1+col2) as total FROM mytable GROUP BY id ORDER BY total HAVING total >= x

Answer 2

You have some misconceptions about SUM. SUM is an aggregating function, means it works on many records and not just one.
To calculate the sum of two fields per record, you should use only the + operator.

SELECT id, col1+col2 AS 'total'
FROM T1
WHERE
(col1+col2+x) >=(SELECT MAX(col1+col2) from T1)