March 24, 2009 at 9:01 am
Hi
I need to select the top 100 member donors. This is based on the TotalPledgedAmount which is the sum of DonationPromisedAmount.
I've written like this but get an error specifying that the subquery returned more than 1 value.
SELECT TOP 100
M.MemberId
,M.MemberForename + ' ' + M.MemberFamilyName AS MemberName
,FP.ProjectName
,FC.CampaignName
,FD.DonationPromisedAmount
,FD.DonationReceivedNet
,'Status' = CASE
WHEN FD.DonationComplete = 1 THEN 'Complete'
WHEN FD.DonationCancelledBy > 0 THEN 'Cancelled'
ELSE NULL
END
,(SELECT SUM(DonationPromisedAmount) FROM FundDonation GROUP BY MemberID) AS TotalPledgedAmount
FROM FundDonation FD
LEFT OUTER JOIN Member M ON FD.OrganisationID = M.OrganisationID AND FD.MemberID = M.MemberID
LEFT OUTER JOIN FundProject FP ON FD.OrganisationID = FP.OrganisationID AND FD.ProjectID = FP.ProjectID
LEFT OUTER JOIN FundCampaign FC ON FD.OrganisationID = FC.OrganisationID AND FD.CampaignID = FC.CampaignID
ORDER BY TotalPledgedAmount DESC
....so I want the result to like this:
MemberID--other columns----TotalPledged
7-------------------------------100000
1--------------------------------90000
200-------------------------------1000
Hope this makes sense.
Thanks....
March 24, 2009 at 9:08 am
try this:
Subqueries in a select can only return one result, you could use a correlated sub-query not sure how that would perform , but here is a another solution:
SELECT TOP 100
M.MemberId
,M.MemberForename + ' ' + M.MemberFamilyName AS MemberName
,FP.ProjectName
,FC.CampaignName
,FD.DonationPromisedAmount
,FD.DonationReceivedNet
,'Status' = CASE
WHEN FD.DonationComplete = 1 THEN 'Complete'
WHEN FD.DonationCancelledBy > 0 THEN 'Cancelled'
ELSE NULL
END
,SUM(DonationPromisedAmount) as TotalPledgedAmount
FROM FundDonation FD
LEFT OUTER JOIN Member M ON FD.OrganisationID = M.OrganisationID AND FD.MemberID = M.MemberID
LEFT OUTER JOIN FundProject FP ON FD.OrganisationID = FP.OrganisationID AND FD.ProjectID = FP.ProjectID
LEFT OUTER JOIN FundCampaign FC ON FD.OrganisationID = FC.OrganisationID AND FD.CampaignID = FC.CampaignID
GROUP BY
M.MemberId
,M.MemberForename + ' ' + M.MemberFamilyName AS MemberName
,FP.ProjectName
,FC.CampaignName
,FD.DonationPromisedAmount
,FD.DonationReceivedNet
,'Status' = CASE
WHEN FD.DonationComplete = 1 THEN 'Complete'
WHEN FD.DonationCancelledBy > 0 THEN 'Cancelled'
ELSE NULL
END
ORDER BY SUM(DonationPromisedAmount) DESC
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 9:12 am
This should get you what you want.
;WITH
CTE1 as
(SELECT
M.MemberId
,M.MemberForename + ' ' + M.MemberFamilyName AS MemberName
,FP.ProjectName
,FC.CampaignName
,FD.DonationPromisedAmount
,FD.DonationReceivedNet
,CASE
WHEN FD.DonationComplete = 1 THEN 'Complete'
WHEN FD.DonationCancelledBy > 0 THEN 'Cancelled'
ELSE NULL
END as [Status]
,(SELECT
SUM(DonationPromisedAmount)
FROM
FundDonation
WHERE
MemberID = FD.MemberID) AS TotalPledgedAmount
FROM
FundDonation FD
LEFT OUTER JOIN Member M
ON FD.OrganisationID = M.OrganisationID AND FD.MemberID = M.MemberID
LEFT OUTER JOIN FundProject FP
ON FD.OrganisationID = FP.OrganisationID AND FD.ProjectID = FP.ProjectID
LEFT OUTER JOIN FundCampaign FC
ON FD.OrganisationID = FC.OrganisationID AND FD.CampaignID = FC.CampaignID),
CTE2 as
(SELECT
*
,Dense_Rank() over (order by TotalPledgedAmount) as PledgeRank
FROM
CTE1)
SELECT
*
FROM
CTE2
WHERE
PledgeRank <= 100;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply