Re: help needed with query

From: Wazusa Blong <fitzjarrell_at_cox.net>
Date: Tue, 12 Jun 2007 12:51:38 -0700
Message-ID: <1181677898.026090.267340@q19g2000prn.googlegroups.com>

On Jun 12, 2:16 pm, EdStevens <quetico_..._at_yahoo.com> wrote:
> Oracle 10.2.0.2.0
>
> I'm having a bit of trouble getting my head wrapped around this one.
>
> create table test_tbl (mgr_id varchar2(5),
> acct_code varchar2(5),
> inv_num number
> inv_amt number);
>
> Each mgr_id can have multiple acct_code. For each mgr_id, we need to
> see the sum
> of all invoice amounts where acct_code like 'A%'
> and sum of all invoice amounts where acct_code like 'B%'
>
> mgr_id act_code_A act_code_B
> ------ ----------- -----------
> smith 123.45 234.56
> jones 345.67 456.78
> snuffy 0.00 901.23
> bilbo 456.78 0.00
>
> not all mgr_id will have both acct_code groups.

Probably not the most efficient, but:

select a.mgr_id,

          nvl(sum(a.inv_amt), 0) act_code_a,
          nvl(sum(b.inv_amt), 0) act_code_b

from test_tbl a, test_tbl b
where b.mgr_id = a.mgr_id
and a.acct_code like 'A%'
and b.acct_code like 'B%'
group by a.mgr_id;

Another option might be:

with acct_cde_a as (
select mgr_id mgr_a, nvl(sum(inv_amt),0) act_code_a from test_tbl
where acct_code like 'A%'
group by mgr_id),
acct_cde_b as (
select mgr_id mgr_b, nvl(sum(inv_amt),0) act_code_b from test_tbl
where acct_code like 'B%'
group by mgr_id)
select mgr_a, act_code_a, act_code_b
from acct_cde_a, acct_cde_b
where mgr_b = mgr_a;

I haven't tested these as I don't have an available playground with which to work. I expect they'll return the proper results.

David Fitzjarrell Received on Tue Jun 12 2007 - 14:51:38 CDT