Find SUM of SOME columns from two mysql tables?

Hi!

So, I have two tables, exam1 and exam2 which looks like these:

Code:

---

table:exam1
 
sid      physics    chemistry      biology
3          80            60            40

---

Code:

---

  table:exam2
sid        physics    chemistry      biology
3            40          50              70

---

Problem:

I want to find the sum of items from both tables (exam1 and exam2), like this

Code:

---

table: total
sid        physics        chemistry      biology
3            120              110            110

---

BUT:

I wont always have to find total of all courses (physics, chemistry, biology), sometimes I may want to find sum of only two, one of all three subjects, so this is really a variable.

Since I program from VB.NET, the list of courses to be totaled can be specified by a user at run time and is kept in a variable say, "courses", so sometimes courses= (physics,chemistry,biology) sometimes courses=(physics,chemistry) and so on.

How can I then find a total of courses that the user selects? (still total of courses in exam1+exam2 tables)

Thanks

Before I answer the question, I need to ask... If both tables have the same columns, then why have multiple tables in the first place?

Hi dday9,

The tables are storing students' scores for two different exams (here knows as exam1 and exam2), at the end of school term, I need to produce a student's report which must show :
- What the student scored in each subject in exam1
- What he scored in each subject in exam2
- and then totals for each subject in that term (for example: physics score in exam1+physics score in exam2)
- After getting the above, I will then find average,grade and student's rank

So I decided to use two tables since they are two different exams. I know I could even have just one table and have a column that says whether its exam1 or exam2 score but I don't know if it will make a bi

I'm open to any suggestions. Thanks.

That is very bad database design, which is why dday9 asked the question. The far more appropriate design would be to have an Exam table with ExamId and ExamName columns and then an ExamScores table with the same schema as your current table but with the addition of an ExamId column that will then be a foreign key to the first table. You then add one record to the Exam table for each exam and one record to the ExamScores table for each set of scores no matter which exam they are for. The easy way to tell that your design is bad and this one is good is to consider what happens if you need to add a third exam. With your current design, that would require adding a new table whereas with the design I'm suggesting you would only need to add a new row to an existing table.