Ok, your example made me think of something and this worked. Based on your example I used:

SELECT City, NULL AS Name, SUM(Num) FROM Table
 GROUP BY City, ORDER BY City

Which gives:

Ontario NULL 1
Houston NULL 6

This is what I want. I want to summarize for each city, not each person in each city. I have to have all 3 fields because this is feeding a report. I just changed the Print When expression in my report.

What confused me the most is that VFP allows the following on VFP tables:

SELECT City, Name, SUM(Num) FROM Table
 GROUP BY City, ORDER BY City

I prefer this. I know the Name field doesn't make sense and I handle it appropriately. I don't need SQL-Server stopping me dead in my tracks because of it.

Thanks to all for the input.

>Ok, you want the sum of Osa.Amt for each 4th-level project.
>
>Would the other fields be the same for

City     Name   Num
>-----    -----  ----
>Ontario  Colin  1
>Houston  Peter  2
>Houston  Joe    4
>
>select city, name, sum(num) from table
> group by city, order by city

>ok, now. what should the result be?

ontario colin 1
>houston peter 6

or

ontario colin 1
>houston joe 6

>you see, neither one makes sense. That's why all non-aggregate fields must be in the GROUP BY, or can't be there at all.
>
>>
>>The Proj_id field is an alphanumeric code in the form of XXXXXX.XXX.XXX.XXX.XX or XXXXXX.XXX.XXX.XXX.XXX.XX Each area between the .'s is a project level. I am attemping to generate a result set that summarizes to level 4 which is represented by the first 18 characters of the Proj_id.
>>
>>>You must include all non-aggregate fields in the GROUP BY clause.
>>>
>>>What is the result set you're after?
>>>
>>>
>>>>

>>>>? SQLEXEC(1,"SELECT {fn LEFT(Osa.PROJ_ID,18)} AS Proj_id, Osa.ACCT_ID, Osa.Trans, SUM(Osa.Amt) AS Amt, Osa.PO_ID, Osa.INVC_ID, Osa.VCHR_NO FROM dbo.OSA Osa WHERE Osa.Amt <> 0 GROUP BY {fn LEFT(Osa.PROJ_ID,18)} ORDER BY 1","cOSA")
>>>>

>>>
>>>>I don't want to include ACCT_ID or any other field in the GROUP BY because that would return a different result set than required.
>>>>
>>>>Any ideas on what to do?