Debasish Choudhury Debasish Choudhury - 2 years ago 62
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:

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