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