I have two identical tables, one for jobs we are currently working on called OpenJobs and another containing invoiced jobs called DoneJobs.

There are many times when I need to get the total number of jobs that match some criteria from both tables. In the following example, I simply need the total number of jobs within a date range:

select cnt(Job) as Jobs from OpenJobs;
where between(GetJob("MaxDate", Job), BDate, EDate) and !deleted();
union all select cnt(Job) as Jobs from DoneJobs;
where between(GetJob("MaxDate", Job), BDate, EDate) and !deleted();
group by 1;
into cursor JobTot

I am trying to get a single record with the total number of jobs. The group by clause here is illegal. You can't group by an aggragate column. So I add an additional field to both selects: "X" as X and group by that column. It produces two records one from OpenJobs and one from DoneJobs.

What syntax can I use to get one record with the total of both tables?