Sql select question

[andy vandenberghe]

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

[mcstock]

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" <hp**************@skynet.be_removethis> wrote in message
news:hp************************************@news.s kynet.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

[VC]

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" <hp**************@skynet.be_removethis> wrote in message
news:hp************************************@news.s kynet.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

[Mr E Guest]

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" <bo*******@hotmail.com> wrote in message news:<C5Tub.191428$275.658505@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" <hp**************@skynet.be_removethis> wrote in message
news:hp************************************@news.s kynet.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

[mcstock]

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" <pi*********@virgin.net> wrote in message
news:a5**************************@posting.google.c om...
| 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" <bo*******@hotmail.com> wrote in message
news:<C5Tub.191428$275.658505@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" <hp**************@skynet.be_removethis> wrote in
message
| > news:hp************************************@news.s kynet.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

[andy vandenberghe]

Hi VC,

Thanks, your code worked allright; here's the real-world translation.

/* list all accounts where the count is not equal */
select a.voucher_no, a.ext_inv_ref, a.count_700500, b.count_400
from (select voucher_no, ext_inv_ref, count(*) count_700500
from agltransact
where client='MG'
and account=700500
and voucher_type in('P4','P5')
and voucher_no >= 63000020
and voucher_no <= 63000024
group by voucher_no, ext_inv_ref) a,
(select voucher_no, ext_inv_ref, count(*) count_400
from agltransact
where client='MG'
and account in('400400','400500','400301','400300')
and voucher_type in('P4','P5')
and voucher_no >= 63000020
and voucher_no <= 63000023
group by voucher_no, ext_inv_ref) b
where a.ext_inv_ref = b.ext_inv_ref
and a.voucher_no=b.voucher_no
and a.count_700500 != b.count_400;

Hrgds,
andy

In article <C5Tub.191428$275.658505@attbi_s53>,
"VC" <bo*******@hotmail.com> wrote:

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.

[Mr E Guest]

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" <mc*************@spamdamenquery.com> wrote in message news:<oI********************@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" <pi*********@virgin.net> wrote in message
news:a5**************************@posting.google.c om...
| 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" <bo*******@hotmail.com> wrote in message
news:<C5Tub.191428$275.658505@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" <hp**************@skynet.be_removethis> wrote in
message
| > news:hp************************************@news.s kynet.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

[mcstock]

short on storage? my first 'real' project had to be implemented in
interpreted BASIC on a TRaSh-80 running XENIX (BASIC because the company
president felt he understood it) -- to conserve space we were limited to
2-character variable names, and no (as in absolutely no) space between
keywords. so, all comments had to be, well, shall we say, under my breath

"Mr E Guest" <pi*********@virgin.net> wrote in message
news:a5**************************@posting.google.c om...
| 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" <mc*************@spamdamenquery.com> wrote in message
news:<oI********************@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" <pi*********@virgin.net> wrote in message
| > news:a5**************************@posting.google.c om...
| > | 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" <bo*******@hotmail.com> wrote in message
| > news:<C5Tub.191428$275.658505@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" <hp**************@skynet.be_removethis> wrote in
| > message
| > | > news:hp************************************@news.s kynet.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