Re: SQL Assistance - Mailing list pgsql-novice

From Chris Campbell
Subject Re: SQL Assistance
Date
Msg-id 453A24085F801842AEA8D0B6B269065D032BED41AD08@HDMC.cds.local
Whole thread Raw
In response to Re: SQL Assistance  (David Raznick <david.raznick@okfn.org>)
List pgsql-novice

 

On Wed, May 8, 2013 at 7:45 PM, Chris Campbell <ccampbell@cascadeds.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@cascadeds.com

 From: kindly@gmail.com [mailto:kindly@gmail.com] On Behalf Of David Raznick
Sent: Wednesday, May 08, 2013 7:35 PM
To: Chris Campbell
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] 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

 

 

Thanks David.  This is exactly what I was looking for. 

 

Regards,

 

Chris

 

 

pgsql-novice by date:

Previous
From: David Raznick
Date:
Subject: Re: SQL Assistance
Next
From: Jayadevan M
Date:
Subject: Re: SQL Assistance