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.

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

Great idea!! that will work well for reports that don’t have to be absolutely current.
Thanks.

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 ( http://itknowledgeexchange.techtarget.com/beyond-excel/forward/ )

…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