Re: Sql select question

From: Mr E Guest <piano.tuner_at_virgin.net>
Date: 21 Nov 2003 13:43:31 -0800
Message-ID: <a5592607.0311211343.46752d30_at_posting.google.com>


mcs,

It's a fair cop, guv - I'm always guilty of leaving comments out - totally agree with explicitly including account 6040 (here I blame the days when our sole server was very short on space!). The solution is more cunning than logical, I guess - desperate to make it as short as possible - doesn't help on the clarity front, but it makes me feel better. ;-)

Shaun.

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:<oIqdnRvxeLqYsCOiRVn-uw_at_comcast.com>...
> 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 - 22:43:31 CET

Original text of this message