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_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 Wed Nov 19 2003 - 23:27:55 CET