By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Find sum of SUM columns from two mysql tables?

P: 1
Hi!

So, I have two tables, exam1 and exam2 which looks like these:
table:exam1
Expand|Select|Wrap|Line Numbers
  1. sid       physics     chemistry       biology
  2. 3           80           60              40
  3.  
table:exam2
Expand|Select|Wrap|Line Numbers
  1. sid        physics    chemistry       biology
  2. 3            40           50             70
  3.  

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

table: total
Expand|Select|Wrap|Line Numbers
  1. sid         physics         chemistry       biology
  2. 3               120            110            110
  3.  
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 26 '16 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,031
This is a question about MySQL, and not about dotnet ;)

u need UNION, short example:
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 UNION ALL SELECT 2;
This produces a result which has 1 and 2 in it.
The keyword ALL determines if duplicate should be kept or ignored.

In your case:
Expand|Select|Wrap|Line Numbers
  1. SELECT sum(physics),sum(chemistry) FROM (
  2.    SELECT * FROM exam1
  3.    UNION ALL
  4.    SELECT * FROM exam2 ) X
  5. GROUP BY X.sid;
  6.  
Dec 17 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.