CODEWITHSUNDEEP
phpmysqlunion
Debasish Choudhury
Debasish Choudhury

Reputation: 83

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?

Upvotes: 0

Views: 2084

Answers (1)

spencer7593
spencer7593

Reputation: 108370

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

Upvotes: 4

Related Questions