Where would you place the 2nd GROUP BY? Does this work in SQL databases [Oracle, SQL Server], or just a VFP feature? I am fairly certain you can not do this [both Group By and referencing] in Oracle, but I will certainly try this out when I get back to work next week.
>Hi Mark,
>
>Actually you
can have more than one GROUP BY, and do
not have to reference it by number.
>
>>In a UNION, you can only have 1 GROUP BY and 1 ORDER BY and each has to e after the last UNION SQL. You also have to reference the GROUP BY and ORDER BY items by number. How about something like:
>>select Field1, sum(Field2) from Table1;
>> where Field3 <> "XXX";
>>union select Field1, sum(Field2) from Table2;
>> where Field3 <> "XXX";
>> and Field1 not in (select field1 from table1);
>> group by 1
>>
Mark McCasland
Midlothian, TX USA