Sherry,

I see several problems with this SQL. As a matter of fact I'm surprised it didn't error out.

1) You have a field from Brokprod in your group by clause, yet no fields from Brokprod are in your select

2) Any fields not involved in a sum, must be included in the group by clause.

A simple example:

Select name, address, city, zip, sum(bill) as bill from mytable ;
group by name, address, city, zip ;
into cursor mycursor

Name,address, city, zip are all in the group by because they are not being summed.

PF


>Hello,
> I have the following sql statement which is not working properly. It doubles up the total for the fields that I want to sum:
> SELECT OUT.*,
> SUM(IIF(RPT_YEAR = "1997",PL_TOT,000000)) AS P97,;
> SUM(IIF(RPT_YEAR = "1997",CL_TOT,000000)) AS C97,;
> SUM(IIF(RPT_YEAR = "1997",TOTAL,000000)) AS T97,;
> SUM(IIF(RPT_YEAR = "1998",PL_TOT,000000)) AS P98,;
> SUM(IIF(RPT_YEAR = "1998",CL_TOT,000000)) AS C98,;
> SUM(IIF(RPT_YEAR = "1998",TOTAL,000000)) AS T98, ;
> SUM(IIF(RPT_YEAR = "1999",PL_TOT,000000)) AS P99,;
> SUM(IIF(RPT_YEAR = "1999",CL_TOT,000000)) AS C99,;
> SUM(IIF(RPT_YEAR = "1999",TOTAL,000000)) AS T99 ;
> FROM BROKPROD,OUT;
> WHERE BROKPROD.AG_ID = OUT.AG_ID ;
> GROUP BY BROKPROD.AG_ID ;
> ORDER BY BROKPROD.AG_ID ;
> INTO TABLE &OUTPUT
>Can anyone tell me what I did wrong ??
>thanks
>sherry