-
Oct 26th, 2016, 04:28 PM
#1
Thread Starter
New Member
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
-
Oct 26th, 2016, 04:36 PM
#2
Re: Find SUM of SOME columns from two mysql tables?
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?
-
Oct 26th, 2016, 04:48 PM
#3
Thread Starter
New Member
Re: Find SUM of SOME columns from two mysql tables?
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.
-
Oct 26th, 2016, 10:31 PM
#4
Re: Find SUM of SOME columns from two mysql tables?
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|