Invalid column name on sum

I am trying to perform sql statement

select buyer, issuer, ident, sum(qnty) as sum1
from palete
where sum1 <> 0
group by buyer, issuer, ident

and I am getting error Invalid column name 'sum1'.

Problem is th开发者_如何学Cat I am getting a lot of results, so I would like to avoid these results where sum1 is 0.

Thanks for help!

Alternate, without the subquery:

select buyer, issuer, ident, sum(qnty) as sum1 
from palete 
group by buyer, issuer, ident 
having sum(qnty) <> 0

Aliases aren't recognised in where clauses. You need to do it like this:

select buyer, issuer, ident, sum(qnty) as sum1 
from palete 
group by buyer, issuer, ident 
having sum(qnty) <> 0

select * from
(
  select buyer, issuer, ident, sum(qnty) as sum1 from palete group by buyer, issuer, ident
) as T1 where T1.sum1 <> 0

You would need to use HAVING clause instead of WHERE clause.

The SQL HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. The HAVING clause behaves like the WHERE clause, but is applicable to groups - the rows in the result set representing groups. In contrast the WHERE clause is applied to individual rows, not to groups.

Hope this helps.