SQL sum() with where

PietaKit

35 pts.

Tags:

AS/400

AS/400 SQL

AS/400 SQL: How can I use a sum() to search for records in a file? I am trying to use the SQL statement to sum up a total and use the total to compare to another record in the same file. My SQL statement is: select w1styl,sum(w1actc) from fileA where w1actc > 0 and sum(w1actc) <= (select w1remc from filea where wmactc = 0) group by w1styl This sums up the total amount [sum(w1actc)] by STYL and hunts for another record where the sum is less than the remaining amount [w1remc] of another record and nothing in the location [w1actc=0]. The goal is to find multiple locations in a location file that can fit in another location.

Software/Hardware used:
as/400 SQL

Asked: August 28, 2012 2:05 AM Last updated: August 28, 2012 6:06 PM

Answer Wiki

Discuss This Question: 3 Replies

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,495 pointsBadges:

report
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:

report
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:

report