My PHP application manages the sending of Greetings from one User to another. I want a query that will tell me how many Greetings were sent to a given User, and what their total value was.
It gets a little tricky.
A Greeting itself does not have value. A Greeting may have a Gift tied to it; a Gift has a fixed value. On top of that: a Greeting does have a "donationAmount" field; if it has a Gift, there may be an amount in there, that should be added to the value of the Gift that is attached to it.
idRecipent (FK to tblUsers)
idGift (FK to tblGifts)
I want a single recordset that returns a count of all the Greetings sent to tblGreetings.idRecipent, and a sum of tblGifts.giftValue and tblGreetings.donationAmount for all those Greetings.
, COUNT(*) AS count_Greetings
, SUM(tblGifts.giftValue) AS total_giftValue
, SUM(tblGreetings.donationAmount) AS total_Donations
ON tblGifts.idGifts = tblGreetings.idGift