Re: SQL Assistance

Something like this -

with pledge_total as (select accountname, appealname ,
Sum(ta.appealgiftamount) pledgetotal
from
ds1.tranappeal ta where ta.fk_gifttype=1
GROUP BY ta.accountname, ta.appealname ),
paymenttotal as (select accountname, appealname , Sum(ta.appealgiftamount)
paymenttotal from
ds1.tranappeal ta where ta.fk_gifttype=3
GROUP BY ta.accountname, ta.appealname )
select accountname, appealname, appealgiftamount, pledgetotal, paymenttotal
from
ds1.tranappeal left outer join pledge_totalpt on
left outer join paymenttotal pyt on

On Thu, May 9, 2013 at 12:15 AM, Chris Campbell <ccampbell(at)cascadeds(dot)com>wrote:

> Greetings,****
>
> ** **
>
> I’m struggling with a bit of SQL here and am looking for ideas on how to
> resolve it. ****
>
> ** **
>
> Given the following query:****
>
> ** **
>
> Select ta.accountname, ta.appealname,****
>
> coalesce(CASE WHEN ta.fk_gifttype=1 THEN Sum(ta.appealgiftamount) ELSE
> 0::decimal END,0) as pledgetotal,****
>
> coalesce(CASE WHEN ta.fk_gifttype=3 THEN Sum(ta.appealgiftamount) ELSE
> 0::decimal END,0) as paymenttotal****
>
> FROM ds1.tranappeal ta****
>
> GROUP BY ta.accountname, ta.appealname, ta.fk_gifttype****
>
> ORDER BY accountname****
>
> ** **
>
> What I want to end up with is one row per account name with the correct
> pledge and payment total. ****
>
> ** **
>
> What I’m receiving in most cases is multiple lines per account name. The
> reason is because I’m being forced to include the fk_gifttype field in the
> Group By. Because there are other gifttype codes, those rows are being
> included with zero amounts. ****
>
> ** **
>
> I also tried using a sub select but that didn’t work because “appealname”
> isn’t part of a scope. As a result, I received the “total” pledge and
> payment regardless of appealname.****
>
> ** **
>
> I’m wonder what other options I might have to get the desired results. **
> **
>
> ** **
>
> Thank you,****
>
> ** **
>
> Chris Campbell****
>
> Cascasde Data Solutions Inc.****
>
> ccampbell(at)cascadeds(dot)com****
>
> ** **
>