Re: SQL Problem.

This should work, unless I misunderstand the problem:

select

   AccountNo,
   max(Decode(substr(Acct_code,1,6),'xxxxxx',Acct_code,null)),    sum(Units)
from

   table
group by

   AccountNo;

ashesh48_at_my-dejanews.com wrote:

> Hi All,
> I stuck in the following type of query.
> Lets say i have following table...
>
> AccountNo Acct_code Units
> -------------------------------------------------------------------------
> 123456789 000001 1000
> 123456789 xxxxxxx 2000
> 123456789 000002 3000
> 123456789 000003 1000
> 100000001 000004 2000
> 100000001 000005 500
> 100000002 000006 2000
> 100000003 xxxxxx 500
>
> We need the output like if the Account No. has acct_code = xxxxxxxx then
> print the that as acct_code and sum all the Units. Otherwise if we dont have
> Acct_code = xxxxxxxx then dont print anything in Acct_code column and just
> print only one record...
>
> The Output is should be as folllow...
>
> AccountNo Acct_code Units
> -------------------------------------------------------------------------
> 123456789 xxxxxxx 7000
> 100000001 2500
> 100000002 2000
> 100000003 xxxxxx 500
>
> I am getting problem with the AccountNo 123456789. I am getting two records
> because of use of Union and DECODE. But i need only one record out of it. ...
>
> Thanks for your reply in advance.
> Ashesh
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Wed Aug 12 1998 - 10:11:39 CDT