Re: SQL Assistance

Hello

I may have misunderstood the intent of your query but I think the following
is equivalent. If you put the sum on the outside you do not have to group
by fk_giftype.

Select ta.accountname, ta.appealname,

sum(CASE WHEN ta.fk_gifttype=1 THEN ta.appealgiftamount ELSE 0::decimal
END) as pledgetotal,

sum(CASE WHEN ta.fk_gifttype=3 THEN ta.appealgiftamount ELSE 0::decimal
END) as paymenttotal

FROM ds1.tranappeal ta

GROUP BY ta.accountname, ta.appealname

ORDER BY accountname

Thanks

David

On Wed, May 8, 2013 at 7:45 PM, 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****
>
> ** **
>