Results 1 to 2 of 2

Thread: selective sum?

  1. #1
    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:

      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
      (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:

    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?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I don't clearly understand what you want to acheive, so consider this a SWAG but:
    Sum (CASE WHEN 1 = g.giftAid
       THEN i.giftValue * 0.282 END
    ) AS totalGiftAid
    At least it should give you some clues if it isn't what you want.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts