carlosdl Aug 28, 2012   3:12 AM GMT

You can't use aggregate functions in the WHERE clause. Take a look at the HAVING clause.

84,720 pointsBadges:

PietaKit Aug 28, 2012   3:47 AM GMT

Thanks.I was able to make the select statement work using the having clause. However, it ran into the error of finding more than one row that met the condition 'Result of select more than one row'. Is there a way to disregard how many rows were selected with a true/false condition? In other words, I dont care how many rows were selected, I just want to know if the condition is true.

35 pointsBadges:

TomLiotta Aug 28, 2012   4:30 AM GMT

I dont care how many rows were selected, I just want to know if the condition is true.   What condition? That sub-SELECT only has one condition -- where wmactc = 0. But I'm pretty sure that's not the condition that you mean.   There might be dozens, hundreds or thousands of rows "where wmactc = 0". Which one do you want to test against the sum() value? That's what you need to decide. SQL needs to know which one should be tested to determine if sum(w1actc) is less than that row.   Some of the rows might be greater than; some might be less than. If you don't tell SQL which row to compare against, it has no way to tell when the condition is true.   You have a value -- sum(w1actc). And then you have a set of rows -- select w1remc from filea where wmactc = 0. It looks as if you're trying to compare a single value against a set of values.   Let's say that sum(w1actc) comes out as (5), and your sub-select finds three rows with the values (3, 5, 7). Does that mean that the condition is true or false? It seems it would be both.   If you want to know if your sum is less than all of the values, use MIN(w1remc) in your sub-select. Or if you want to know if it's less than any of the values, use MAX(w1remc).   Tom

125,585 pointsBadges: