CODEWITHSUNDEEP
phpmysql
Ahmed ElGamil
Ahmed ElGamil

Reputation: 179

limit result upon sum of two column

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

Upvotes: 1

Views: 85

Answers (3)

jospratik
jospratik

Reputation: 1674

This answer is related to Ahmed's comment:

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

Upvotes: 1

Taryn
Taryn

Reputation: 247720

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.

Upvotes: 2

Matthew FitzGerald-Chamberlain
Matthew FitzGerald-Chamberlain

Reputation: 564

I believe you are looking for the HAVING clause.

So, 

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

Upvotes: 4

Related Questions