1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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.
    SEMrush
    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
    SEMrush