Hi Paul,

In SQL Server you would use a derived table. Something like

SELECT < whatever > FROM 
( < your first select here > ) dt 
WHERE ...
GROUP by dt.Region, ...

>I have the following statement

>SELECT DISTINCT (case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal ,
>(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal ,
>(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total ,
>(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total ,
>(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total , 
master.Region, bfblue_data.jobDesc, master.season, 'all' AS type
>FROM         bfblue_data INNER JOIN
>                      master ON bfblue_data.FAIR_ID = master.FairNumber
>GROUP BY master.Region, bfblue_data.jobDesc, master.season
>UNION
>SELECT DISTINCT
> (case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal ,
>(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal ,
>(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total ,
>(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total ,
>(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total ,
> master.Region, bfblue_data.jobDesc, master.season, 'week' AS type
>FROM         bfblue_data INNER JOIN
>                      master ON bfblue_data.FAIR_ID = master.FairNumber 
AND bfblue_data.updateddate > dateadd(day,-30,getdate())
>GROUP BY master.Region, bfblue_data.jobDesc, master.season
>ORDER BY  type, master.season,master.Region, bfblue_data.jobDesc
>
>Results
>falltotal   springtotal v2total     v3total     v1total     Region      jobDesc              season     type
>----------- ----------- ----------- ----------- ----------- ----------- -------------------- ---------- ----
>582         0           582         0           0           1           SBF4154 (Blue, V2)   fall       all
>181         0           0           0           181         1           SBF4154 (Red, V1)    fall       all
>34          0           0           34          0           1           SBF4154 (Red, V3)    fall       all

>
>And what I would like to do is group the result set from the above statement. Any ideas?? If I was using Foxpro i would just run the first select statement into a cursor and then run another againt the cursor to group it again. I dont know how to do that in SQL Server..
>
>Thanks
>
>Paul