Sql select question

**mcstock** · Jun 27 '08, 05:33 PM

Re: Sql select question

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" <hp275_removeth is@skynet.be_re movethiswrote in message
news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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 · Jun 27 '08, 05:33 PM

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_removeth is@skynet.be_re movethiswrote in message
news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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** · Jun 27 '08, 05:33 PM

Re: Sql select question

This is abit shorter...

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

Shaun.

"VC" <boston103@hotm ail.comwrote in message news:<C5Tub.191 428$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" <hp275_removeth is@skynet.be_re movethiswrote in message
news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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** · Jun 27 '08, 05:33 PM

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@vi rgin.netwrote in message
news:a5592607.0 311210057.25dad a12@posting.goo gle.com...
| This is abit shorter...
|
| select ex_inv_ref
| from agltransact
| where account in (1512, 6040)
| having sum(decode(acco unt, 1512, 1, -1)) <0
| group by ex_inv_ref;
|
| Shaun.
|
| "VC" <boston103@hotm ail.comwrote in message
news:<C5Tub.191 428$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" <hp275_removeth is@skynet.be_re movethiswrote in
message
| news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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** · Jun 27 '08, 05:33 PM

Re: Sql select question

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','40 0500','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$2 75.658505@attbi _s53>,
"VC" <boston103@hotm ail.comwrote:

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** · Jun 27 '08, 05:33 PM

Re: Sql select question

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" <mcstockspamplu g@spamdamenquer y.comwrote in message news:<oIqdnRvxe LqYsCOiRVn-uw@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@vi rgin.netwrote in message
news:a5592607.0 311210057.25dad a12@posting.goo gle.com...
| This is abit shorter...
|
| select ex_inv_ref
| from agltransact
| where account in (1512, 6040)
| having sum(decode(acco unt, 1512, 1, -1)) <0
| group by ex_inv_ref;
|
| Shaun.
|
| "VC" <boston103@hotm ail.comwrote in message
news:<C5Tub.191 428$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" <hp275_removeth is@skynet.be_re movethiswrote in
message
| news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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** · Jun 27 '08, 05:33 PM

Re: Sql select question

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" <piano.tuner@vi rgin.netwrote in message
news:a5592607.0 311211343.46752 d30@posting.goo gle.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" <mcstockspamplu g@spamdamenquer y.comwrote in message
news:<oIqdnRvxe LqYsCOiRVn-uw@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@vi rgin.netwrote in message
| news:a5592607.0 311210057.25dad a12@posting.goo gle.com...
| | This is abit shorter...
| |
| | select ex_inv_ref
| | from agltransact
| | where account in (1512, 6040)
| | having sum(decode(acco unt, 1512, 1, -1)) <0
| | group by ex_inv_ref;
| |
| | Shaun.
| |
| | "VC" <boston103@hotm ail.comwrote in message
| news:<C5Tub.191 428$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" <hp275_removeth is@skynet.be_re movethiswrote in
| message
| | news:hp275_remo vethis-59987E.22531619 112003@news.sky net.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(acc ount)/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

Sql select question

Sql select question

Comment

Comment

Comment

Comment

Comment

Comment

Comment