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:
Code:
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
I need to build on this: I need to add a total for Gift Aid.
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:
Code:
SUM (
(
gi.giftValue WHERE g.giftAid = 1
+
g.giftDonationAmount WHERE g.giftAid = 1
)* 0.282
) AS totalGiftAid
... but clearly that's not working; I have no idea, however, how it should be structured. Can anyone help?
dBforums
>
selective sum?
06-25-09, 05:49
Linear Mode
