From: boston103@hotmail.com   
      
   Hello andy,   
      
   Given:   
      
   create table t1(ex_inv_ref int,   account int);   
      
   the simplest way to do what you want is:   
      
   select a.ex_inv_ref, count_6040, count_1512 from   
     (select ex_inv_ref, count(*) count_6040 from t1 where account=6040 group   
   by ex_inv_ref) a,   
     (select ex_inv_ref, count(*) count_1512 from t1 where account=1512 group   
   by ex_inv_ref) b   
   where a.ex_inv_ref=b.ex_inv_ref and count_6040 != count_1512;   
      
   Rgds.   
      
      
   "andy vandenberghe"  wrote in message   
   news:hp275_removethis-59987E.22531619112003@news.skynet.be...   
   > Hello everybody,   
   >   
   > i have the following table (agltransact), in which 2 fields are relevant:   
   >   
   > ex_inv_ref  account   
   > 15          1512   
   > 15          6040   
   > 16          1512   
   > 16          1512   
   > 16          6040   
   > 16          6040   
   > 17          1512   
   > 17          1512   
   > 17          1512   
   > 17          6040   
   > 17          6040   
   > 18          1512   
   > 18          1512   
   > 18          6040   
   > 18          6040   
   > 18          6040   
   > 18          6040   
   >   
   > I would like to select the ext_inv_value for which there is not an   
   > *equal* number of accounts 1512 and 6040;  so this is :   
   >   
   > ext_inv_ref   
   > -----   
   > 17   
   > 18   
   >   
   > I tried   
   >   
   > select ext_inv_ref,   
   > from agltransact   
   > where client='MG' and account in('1512','6040') and ext_inv_type >= 15   
   > and ext_inv_type <= 17   
   > group by ext_inv_ref   
   > having round(count(account)/2,0) != count(account)/2;   
   >   
   > this select only those ext_inv_ref with an uneven number of accounts, so   
   > ext_inv_ref   
   > -----------   
   > 17   
   >   
   > how would you write a query like that ?   
   >   
   > thanks,   
   > Andy   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)