Aggregate SUM

when using the aggregste function (sum) in a report.... is there a way to place a conditional statemment within (i.e.) sum(col1 where col1 is not null)

David, I seem to recall tackling this problem in the distant past. I assume that you are coding a standard Informix 4GL report script. If so, one way of achieving what you are after is to replace NULLS with ZEROS in the column(s) (record fields) you want to sum after the records are fetched from the database, but before they are passed to the report. That is, fetch the record, check for NULLS, and if found replace them with ZEROS. This will allow the sum to function properly. This assumes of course that you don't need to distinguish between records that contain NULLS or ZEROS in those column(s). HTH, Brett David Tucker wrote: > when using the aggregste function (sum) in a report.... > > is there a way to place a conditional statemment within > (i.e.) > sum(col1 where col1 is not null)

My question was a little more general than you thought. I don't need to identify nulls. I would just like know if you can have conditions within the aggregate function SUM. (i.e. SUM(col1 WHERE col2 = "01") Thanks for your time.... though Brett Randall (REMOVECAPITALSbrett_s_r@hotmail.com) wrote: : David, : I seem to recall tackling this problem in the distant past. : I assume that you are coding a standard Informix 4GL report script. If : so, one way of achieving what you are after is to replace NULLS with : ZEROS in the column(s) (record fields) you want to sum after the records : are fetched from the database, but before they are passed to the : report. That is, fetch the record, check for NULLS, and if found : replace them with ZEROS. This will allow the sum to function properly. : This assumes of course that you don't need to distinguish between : records that contain NULLS or ZEROS in those column(s). : HTH, : Brett : David Tucker wrote: : > when using the aggregste function (sum) in a report.... : > : > is there a way to place a conditional statemment within : > (i.e.) : > sum(col1 where col1 is not null)

David, Thanks for clarifying. I don't think that you can use the SUM function in the way that you intend, that is directly on the column itself. Your options are :- 1) If using SQL, use the where clause to limit to rows of a particular criteria. I can't imagine that this will be much use to you though, as it appears that you want a conditional sum without limiting records. 2) Maintain a variable within the report script, and conditionally sum the column depending on the conditional column. Be sure to reset the summing variable when required. From (distant) memory, if you make the additional variable a member of the report record structure, then you can use it in aggregate sections of the report as a normal column (eg after group of etc.). Cheers, Brett David Tucker wrote: > My question was a little more general than you thought. > > I don't need to identify nulls. I would just like know if > you can have conditions within the aggregate function SUM. > > (i.e. SUM(col1 WHERE col2 = "01") > > Thanks for your time.... though

David Tucker wrote: > > My question was a little more general than you thought. > > I don't need to identify nulls. I would just like know if > you can have conditions within the aggregate function SUM. > > (i.e. SUM(col1 WHERE col2 = "01") > If the version you are using supports CASE, then you should be able to use a CASE expression as an argument, e.g. SUM(CASE WHEN condition THEN NULL ELSE col1 END)

David Tucker wrote: > when using the aggregste function (sum) in a report.... > > is there a way to place a conditional statemment within > (i.e.) > sum(col1 where col1 is not null) As several people correctly pointed out, aggregates other than COUNT(*) ignore nulls. Somewhat to my surprise, no-one pointed out that you can compute aggregates within the body of the I4GL (or ACE) report: LET decvar = SUM(r.col1) WHERE r.col2 MATCHES "*ABC*" AND r.col4 > 29 You can use the aggregates direct in a PRINT statement too. When I do that, though, I always enclose the entire expression in parentheses, to ensure that it is not misinterpreted. Only compute aggregates from values passed into the report. Trying to compute aggregates based on global variables of report-local variables which are not passed into the report is error-prone. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>