From: mcstockspamplug@spamdamenquery.com   
      
   this is a good application of decode -- do your GROUP BY and use DECODE   
   twice in the HAVING clause -- both nested in a COUNT() functions, one which   
   computes a count of #1512 accounts, one that computes a count of #6040   
   accounts -- your HAVING clause should qualify rows where these counts are   
   not equal   
      
   see recent references to PIVOT queries for similar examples   
      
   let me know if you need further help   
      
   -- mcs   
      
   "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)