Re: Comparing two detail totals in SQL

[Pete Helgren <Pete@xxxxxxxxxx>]

Oops The opening statement is *wrong* I meant:

I need to compare a total from a set of detail records to another set of detail records that contain a single total.

You never see these things until you press send......


Pete Helgren wrote:
> I need to compare a total from a set of detail records to a record that 
> contains a single total.  Actually, I am looking for records that 
> *don't* match.
>
> I can get the totals like this from each record set:
>
> select empID1, code1, sum(quantity) as tot1
> from rec1 where recDate Between
> 070701 and 080410
> group by empID1, code1
> order by empID1,code1
>
> select empID2, code2, sum(quantity) as tot2
> from rec2
> group by empID2, code2
> order by empID2,code2
>
> This works well.  But I need to compare the total fields from each query 
> and select the records where the totals don't match (tot1 <> tot2).  
> Both queries have the EmpID and code fields in common.
>
> I think I need to use the queries with the "as 'table'"  keyword but I 
> can't get my head around the join/compare logic.  Is there a simpler way 
> to do the compare?
>
> Thanks,
>
> Pete Helgren
>
>