Обсуждение: SQL Assistance
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
On Wed, May 8, 2013 at 2: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 > > This is an ugly way to do it, hopefully someone else has something better select foo.accountname, foo.appealname, case when bar.pledgetotal is null then 0.0 else bar.pledgetotal end as "pledgetotal", case when bazz.paymenttotal is null then 0 else bazz.paymenttotal end as "paymenttotal" from (select distinct ta.accountname, ta.appealname from ds1.tranappeal ta where ta.fk_gifttype in (1, 3)) foo left join (select ta.accountname, ta.appealname, sum(ta.appealgiftamount) as pledgetotal from ds1.tranappeal ta where ta.fk_gifttype = 1 GROUP BY ta.accountname, ta.appealname ) bar on foo.accountname = bar.accountname and foo.appealname = bar.appealname left join (select ta.accountname, ta.appealname, sum(ta.appealgiftamount) as paymenttotal from ds1.tranappeal ta where ta.fk_gifttype = 3 GROUP BY ta.accountname, ta.appealname ) bazz on foo.accountname = bazz.accountname and foo.appealname = bazz.appealname ORDER BY foo.accountname Does that help? Hopefully someone else has something more efficient. -Mike
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
David
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.
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.
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
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.