Re: Sql select question

This is abit shorter...

select ex_inv_ref
from agltransact
where account in (1512, 6040)
having sum(decode(account, 1512, 1, -1)) <> 0 group by ex_inv_ref;

Shaun.

"VC" <boston103_at_hotmail.com> wrote in message news:<C5Tub.191428$275.658505_at_attbi_s53>...
> 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 Fri Nov 21 2003 - 09:57:32 CET