Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    17

    Unanswered: Aggregating child records by Type

    I have a parent table: Deposits (Depid1, Date) and a Child table Depositsdetails (FormOfPmt, Amount) that stores the various payments and form of payments received for each day. The form of payment can be cash, check, DirDepo. I would like to aggregate for each parent record the sum of cash, check, DirDepo. I am currently solving this with with:
    ------------
    SELECT deposits.Depid1, deposits.DepDate, sum(if(depodetails.Pmttype='cash', depodetails.AmtDeposited,0)) as TotCash, sum(if(depodetails.Pmttype='check', depodetails.AmtDeposited,0)) as TotCheck, sum(if(depodetails.Pmttype='DirDepo', depodetails.AmtDeposited,0)) as TotDirDepo
    JOIN depodetails depodetails ON deposits.Depid1 = depodetails.Depid2 GROUP BY deposits.Depid1
    ------------
    I get the amounts I need. However I am not sure it's the best approach. I would appreciate any comment/advice if there is a better approach. Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your way is fine, it gives one row per depid1

    here's another way --
    Code:
    SELECT deposits.Depid1
         , deposits.DepDate
         , depodetails.Pmttype
         , SUM(depodetails.AmtDeposited) as Total
      FROM deposits
    INNER
      JOIN depodetails 
        ON depodetails.Depid2 = deposits.Depid1 
    GROUP 
        BY deposits.Depid1
         , depodetails.Pmttype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    17
    OK. thanks Rudy

Posting Permissions

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