Results 1 to 2 of 2

Thread: selective sum?

  1. #1
    Join Date
    Jan 2002
    Posts
    189

    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:

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    At least it should give you some clues if it isn't what you want.

    -PatP
    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
  •