Debasish Choudhury Debasish Choudhury - 1 year ago 53
MySQL Question

Simple way to calculate average by union of three table in MySQL

I have a union of three tables (t1,t2,t3). Each return exactly the same number of records, first column is id, second amount:

1 10
2 20
3 20

1 30
2 30
3 10

1 20
2 40
3 60


Is there a simple in SQL way to calculate the average up to only get:

1 20
2 30
3 30


can the new value store in an another table t4 with php code?

Answer Source

One way to get an average is to use the SQL AVG() aggregate function.

If we want an average "per id", then we need to include a GROUP BY clause.

We can use an inline view query in place of a table reference. (MySQL refers to this as a derived table.

Here's an example of what the query might look like:

SELECT t.id
     , AVG(t.amount) AS avg_amount
  FROM ( SELECT t1.id
              , t1.amount
           FROM t1
          UNION ALL
         SELECT t2.id
              , t2.amount
           FROM t2
          UNION ALL
         SELECT t3.id
              , t3.amount
           FROM t3
       ) t
 GROUP BY t.id 
 ORDER BY t.id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download