Total Calculations, post to Dbase table

[toad78]

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

 

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

 

I would appreciate anyone helping this newb finish this one up.

 

Thank you!

[Maq]

I'm going to assume this is a MySQL database.  So I will go ahead and move it there.

 

You can return the total by using the aggregate function, SUM().