Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: COUNT and SUM in the same query?

    Not sure if, or how, I can do this.

    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.

    To summarise:

    Code:
    tblGreetings
    	idGreeting (PK)
    	idRecipent (FK to tblUsers)
    	idGift (FK to tblGifts)
    	donationAmount (DOUBLE)
    
    tblGifts
    	idGifts (PK)
    	giftValue (DOUBLE)
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT tblGreetings.idRecipent
         , COUNT(*) AS count_Greetings
         , SUM(tblGifts.giftValue) AS total_giftValue
         , SUM(tblGreetings.donationAmount) AS total_Donations
      FROM tblGreetings
    LEFT OUTER
      JOIN tblGifts
        ON tblGifts.idGifts = tblGreetings.idGift
    GROUP
        BY tblGreetings.idRecipent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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