AS/400 query

30 pts.
AS/400 Query
AS/400 Reports
Is there a way to create a query that gives you a report with an average price, but does not include the zero prices in the count of the average? Those "zero price records" must be included in the report for other info.


Software/Hardware used:

Answer Wiki

Thanks. We'll let you know when a new response is added.

Just a thought.
In cases list this, I have moved away from providing users with reports that they ned up wanting to manipulate.
Consider just creating a .csv file.
Users can pull that into Excel and play until long after you have gone home for the day 😉
The can SUM, AVG, Filter, Hide, etc.
I have found the this substantaily cut down requests for “a report exactly like the other one, just a little different”

Discuss This Question: 5  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.
  • CharlieBrowne
    I dop not beleive this could be done in a single query. You would need to create multiple queries. Is there some reason that it has to be done with AS400 query? Is the is one time shot, or something that will run often? Using SQL you can fo a COUNT of non-zero records and a SUM of the amounts so you can calc the average.
    62,385 pointsBadges:
  • Hf
    I will create a program for this report. The users in my office want access to manipulate the report as needed, so I thought the query was the easiest way. Once I have to create multiple queries, I may as well create a program, although I don't enjoy doing the o-spec or rlu with so many numbers.... Thanks
    30 pointsBadges:
  • Hf
    Great idea!! that will work well for reports that don't have to be absolutely current. Thanks.
    30 pointsBadges:
  • Craig Hatmaker
    Just for grins – I wanted to try solving your problem using the AS/400’s SQL (sorry – I haven’t used Query for decades and would be lost trying to use it – I find SQL and Excel works best for me). If you’d like to try it, use STRSQL and create a statement like this: The “Sub Select” exclude zero price records in an average.
    Select A.Product, Avg(A.Price) as AvgWithZeros, Sum(A.Price)/(Select Count(*) From Orders B Where B.Product = A.Product and B.Price > 0) as AvgWithOutZeroes 
    From Orders A 
    Group by A.Product
    I agree with Charlie - Excel is definitely the better way to go. But you can skip the .csv thing and pull data directly into Excel from the AS/400. In my blog I discuss how to create an "easy" button in Excel to extract records from any database - including the AS/400's DB2 database. If you have Client Access, you have an AS/400 ODBC driver and that's all you need. Here is the link: Beyond Excel ( )
    1,860 pointsBadges:
  • TomLiotta
    ...using the AS/400’s SQL With SQL, it would be relatively easy. But the Query/400 product doesn't have a good way to work with boolean results, so it'd be much more difficult. I suppose that a VIEW could be created that presented logical values for Query/400 to work with. The kind of value that would be needed would the logical result of an expression such as {price = 0}. The resulting '0' or '1' logical result would then be used for its numeric value and counted. That way the "zero price" records wouldn't be counted. As usual, though, I wouldn't use Query/400. I'd use a QM query instead. That would combine the power of SQL with a user interface much like Query/400 by using the "Prompted" mode. Tom
    125,585 pointsBadges:

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.

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


Share this item with your network: