Re: help needed with query

On Jun 12, 3: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.

Just for variety, try something like this: SELECT
  MGR_ID,
  SUM(DECODE(SUBSTR(ACCT_CODE,1,1),'A',INV_AMT,0)) ACT_CODE_A,   SUM(DECODE(SUBSTR(ACCT_CODE,1,1),'B',INV_AMT,0)) ACT_CODE_B FROM
  TEST_TBL
GROUP BY
  MGR_ID; If the first character of ACCT_CODE is A, add INV_AMT to the sum, otherwise add 0 to the sum.
If the first character of ACCT_CODE is B, add INV_AMT to the sum, otherwise add 0 to the sum.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 12 2007 - 18:40:16 CDT