Re: Sql select question

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" <hp275_removethis_at_skynet.be_removethis> wrote in message news:hp275_removethis-59987E.22531619112003_at_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
Received on Thu Nov 20 2003 - 00:33:54 CET