I have a union of three tables (t1,t2,t3). Each return exactly the same number of records, first column is id, second amount:
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