SQL sum() with where

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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    You can't use aggregate functions in the WHERE clause. Take a look at the HAVING clause.
    69,990 pointsBadges:
    report
  • PietaKit
    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
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following