CODEWITHSUNDEEP
mysqlsql
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

MySQL view count and sum and other operations from multiple tables

Here is my situation:

I have 4 tables that all contains a column called score in all of these tables my goal for a view to create operations to the result of the 4 tables getting the following values:

Now i know that i would be able to create the view as:

(SELECT * FROM table1 where condition) + (SELECT * FROM table2 where condition)

So on and so forth.

but for each of the three goals i have i would have to nested select all tables atleast 2 times.

So my question is how do you handle a case like this? is there any operation in sql that makes this an easy task or am i bound to do something redundant?

Update

So my full case is that every use in my system has something called a division_id now i want to use this ID to find out what the score is for each division:

enter image description here

(PLEASE IGNORE THE _COPY)

Upvotes: 0

Views: 478

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

You could use a UNION to join the 4 tables, since there is no join condition. There are a couple of ways that you could do this with the division field. Probably the most concise is:

select division_id, count(*), avg(scores.score), sum(scores.score) from
  user join 
  (select id as user_id, score from user
   UNION ALL
   select user_id, score from test_score
   UNION ALL
   select user_id, score from task_score
   UNION ALL
   select user_id, score from offline_score) as scores
  on user.id = scores.user_id
  group by division_id

Link to SQLFiddle

Upvotes: 1

Related Questions