limit result upon sum of two column

Question

Table:

id | c1  | c2
-------------    
1  | 10 | 20
2  | 55 | 20
3  | 30 | 30
4  | 11 | 80
5  | 12 | 20

and query

select (sum(c1)+sum(c2))as sum from mytable where sum > 100 ??

the above generates an error as sum not part of the table. any work around to limit / where condition to restrict result returned to be only upon this condition of two columns sum ? (with out the need to add a third column to the table)

Thank you

Use `HAVING` instead of `WHERE` also `sum` is probably reserved, try another field name. — Orbling, Jun 24 '13 at 17:05
@SylvainLeroux: It is surprising! It's not in the [reserved word list](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) - but I would still not use it, could become reserved in future and confusing in any event. — Orbling, Jun 24 '13 at 17:09

score 4 · Accepted Answer · answered Jun 24 '13 at 17:05

4

I believe you are looking for the HAVING clause.

So,

SELECT (SUM(c1)+SUM(C2)) AS sum FROM mytable HAVING sum > 100;

answered Jun 24 '13 at 17:05

Matthew FitzGerald-Chamberlain

564
5
11

1

@SylvainLeroux Agreed. Truth be told, I'm not sure the point of this query to begin with. That is, this query would make much more sense if it was `SELECT (c1+c2) AS sum FROM mytable...`, as a sum per row. But that wasn't what the question was. – Matthew FitzGerald-Chamberlain Jun 24 '13 at 17:16
and assume i want to add a where condition to be where id > 5 for example ? – Ahmed ElGamil Jun 24 '13 at 17:27
HEY ahmed checkout my answer at bottom regarding id > 5 question – jospratik Jun 24 '13 at 17:48

Taryn · Answer 2 · 2013-06-24T17:11:17.110

2

You can either wrap your existing query in another select:

select total
from
(
  select sum(c1 + c2) as total 
  from mytable 
) d
where total > 100;

Or you can use a HAVING cluase:

select sum(c1 + c2) as total 
from mytable 
having sum(c1 + c2) > 100;  -- you can also use the total alias in the having

See SQL Fiddle with Demo.

edited Jun 24 '13 at 17:11

answered Jun 24 '13 at 17:06

Taryn

224,125
52
341
389

score 1 · Answer 3 · edited Jun 24 '13 at 18:06

1

This answer is related to Ahmed's comment:

SELECT (c1+c2) AS sum FROM mytable WHERE ID > 5  GROUP BY ID HAVING sum > 100;

edited Jun 24 '13 at 18:06

gustavohenke

38,209
13
113
120

answered Jun 24 '13 at 17:47

jospratik

1,286
10
18

limit result upon sum of two column

3 Answers3