Can I use the result of a SUM function in the WHERE clause of MSSQL?

Question

For example suppose I have

SELECT sum(...) as total

Can I do something like

WHERE total > 10

When I try that actual syntax I get an error

invalid column name 'total'

Answer 1

if you are using sum with group by

select sum(...) 
group by Col1 
having sum(...) > 10

without group by

select sum(...) 
having sum(...) > 10 

if you want to use a column name you need to place the query in subquery

SELECT * FROM (
   SELECT sum(...) as total 
) as T
WHERE total > 10

Answer 2

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference".

HAVING is like WHERE with aggregate functions, or you could use a subquery.

select name, sum(amount) as total
from table
group by name
having sum(amount) > 10

Or

select * from(
   select name, sum(amount) as total 
) as temp
where temp.total > 10