Thread: selective sum?
06-25-09, 06:49 #1Registered User
- Join Date
- Jan 2002
Unanswered: selective sum?
Just to briefly outline; in our application, Users can send Greetings to each other on behalf of a selected Charity. A Greeting may include an optional Gift, and if a Gift is included, they may add an additional donation of their choosing.
I've got the following report query that pulls out a list of Charities with the total number of greetings sent on their behalf, along with the total value of Gifts and Donations made to them:
SELECT c.idCharities, c.charityName, COUNT(g.idGreetings) AS totalGreetings, SUM(gi.giftValue) AS giftValueTotal, SUM(g.giftDonationAmount) AS giftDonationTotal FROM Greetings g INNER JOIN Charities c ON g.idCharities = c.idCharities LEFT JOIN Gifts gi ON g.idGifts = gi.idGifts WHERE (g.sendDate > c.lastPaymentDate) OR c.lastPaymentDate IS NULL GROUP BY g.idCharities ORDER BY c.charityName
There is another field in table Greetings; "giftAid", which will be 1 or 0.
If it's 1, I need to add together the gift value for any gift attached to that greeting, and any donation amount that may have been included, and multiply that by a fixed number - currently 0.282 - to find out the total of Gift Aid that should be claimed.
So I need to add something like:
SUM ( ( gi.giftValue WHERE g.giftAid = 1 + g.giftDonationAmount WHERE g.giftAid = 1 )* 0.282 ) AS totalGiftAid
06-25-09, 10:00 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
I don't clearly understand what you want to acheive, so consider this a SWAG but:Code:
Sum (CASE WHEN 1 = g.giftAid THEN i.giftValue * 0.282 END ) AS totalGiftAid
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.