Results 1 to 4 of 4

Thread: Find SUM of SOME columns from two mysql tables?

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2015
    Posts
    10

    Question 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

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    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?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2015
    Posts
    10

    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.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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
  •  



Click Here to Expand Forum to Full Width