Total Calculations, Post to Dbase Table

Discussion in 'PHP' started by toad78, May 10, 2009.

  1. #1
    I have three dbase tables:
    Project: Building a set of tables to record customer information, record selected uses of transportation, record the points earned from using certain types of transportation, record the total points in a database table.

    Currently, these are the tables:
    Code:
    
    TEAM
    teamID
    teamName
    
    LIVE DATA:
    Live Data:
    TEAM
    1       Buttmunch
    ____________________________
    ACTIVITY
    activityID
    activityName
    activityValue
    
    LIVE DATA:
    ACTIVITY
    1       Walking    50
    2       Biking      60
    3       Trolley     10
     
    ____________________________
    ENTRY (this records when the team applied, there TEAM info, and the ACTIVITY they have chosen)
    entryID
    date
    teamID
    activityID
    
    LIVE DATA:
    ENTRY
    1    2009-05-05    1     2
    2    2009-05-05    1     3
    3    2009-05-04    1     1
    
    _______________________________
    TEAM_TOTALS (this is where I get scatter brained)
    tt_ID
    teamID
    activityID
    entryID
    totalPoints
    
    LIVE DATA:
    TOTAL POINTS
    1    1    1    120
    Code (markup):
    I need to know how I can calculate the total and record it in the TOTAL POINTS database table based on teamID.

    Currently, with assistance from others, I have this:
    Code:

    SELECT teamName
         , date
         , activityName
         , activityValue
         , CASE WHEN activityName IS NULL
                THEN 'totals'
                ELSE 'details' 
            END AS sortkey
      FROM (
           SELECT team.teamName
                , entry.date
                , activity.activityName
                , activity.activityValue
             FROM team
           INNER
             JOIN entry
               ON entry.teamID = team.teamID
           INNER
             JOIN activity
               ON activity.ID = entry.activityID
            WHERE team.teamID = 2
           UNION ALL
           SELECT NULL
                , NULL
                , NULL
                , SUM(activity.activityValue) 
             FROM entry
           INNER
             JOIN activity
               ON activity.ID = entry.activityID
            WHERE entry.teamID = 2
           ) AS u
    ORDER
        BY sortkey
         , date
    Code (markup):
    I would appreciate anyone helping this newb finish this one up.

    Thank you!
     
    toad78, May 10, 2009 IP