Re: help needed with query

From: Nis Jørgensen <nis_at_superlativ.dk>
Date: Tue, 12 Jun 2007 23:49:08 +0200
Message-ID: <466f14de$0$90273$14726298@news.sunsite.dk>

EdStevens skrev:
> 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.

Quick, dirty and untested

SELECT mgr_id,

	SUM(CASE WHEN acct_code LIKE 'A%' THEN inv_amt ELSE 0 END) AS act_code_A
	SUM(CASE WHEN acct_code LIKE 'B%' THEN inv_amt ELSE 0 END) AS  act_code_B

GROUP BY mgr_id

/Nis Received on Tue Jun 12 2007 - 16:49:08 CDT