Re: Sql select question

clever answer -- did not look correct at first, since i was expecting a decode for each account value

looks like your decode is incrementing the sum for #1512, and decrementing for 'all others' -- which means #6040; so if they don't balance, you get your rows of interest

a couple code maintenance observations
[_] i am so used to putting GROUP BY before HAVING that i have long forgotten that HAVING can come first -- but for clarity, i would recommend listing GROUP BY first
[_] to make the purpose of the DECODE more clear, you may want to include 6040 explicitly, or include an inline comment on the not-immediately-apparent logic

i'm sure others will have other opinions

• mcs

"Mr E Guest" <piano.tuner_at_virgin.net> wrote in message news:a5592607.0311210057.25dada12_at_posting.google.com...
| 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 - 16:19:15 CET