How to add a column from a similar second table to a SQL table?

Question

MySQL

there are 2 tables, 3 columns in which are identical, and 4 (valuation) are different

mysql> describe oper_survey;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| num       | varchar(20) | YES  |     | NULL    |       |
| operator  | varchar(20) | YES  |     | NULL    |       |
| queue     | varchar(20) | YES  |     | NULL    |       |
| valuation | varchar(20) | YES  |     | NULL    |       |
| date      | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> describe service_survey;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| num       | varchar(20) | YES  |     | NULL    |       |
| operator  | varchar(20) | YES  |     | NULL    |       |
| queue     | varchar(20) | YES  |     | NULL    |       |
| valuation | varchar(20) | YES  |     | NULL    |       |
| date      | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Required data:

mysql> SELECT queue,operator,AVG(valuation) FROM oper_survey WHERE queue = '500' AND date BETWEEN '2021-06-01' AND '2021-06-16' GROUP BY operator ORDER BY operator;
+-------+----------+------------------+
| queue | operator | AVG(valuation)   |
+-------+----------+------------------+
| 500   |          |              2.5 |
| 500   | 970      | 3.85714285714286 |
| 500   | 971      |            4.625 |
| 500   | 972      | 3.45161290322581 |
| 500   | 973      | 4.81818181818182 |
| 500   | 974      | 4.64102564102564 |
| 500   | 975      | 4.65714285714286 |
| 500   | 976      | 4.93478260869565 |
| 500   | 978      |                5 |
| 500   | 979      | 4.90909090909091 |
+-------+----------+------------------+
10 rows in set (0.03 sec)

mysql> SELECT queue,operator,AVG(valuation) FROM service_survey WHERE queue = '500' AND date BETWEEN '2021-06-01' AND '2021-06-16' GROUP BY operator ORDER BY operator;
+-------+----------+------------------+
| queue | operator | AVG(valuation)   |
+-------+----------+------------------+
| 500   | 970      |                4 |
| 500   | 971      | 4.63636363636364 |
| 500   | 972      | 3.23684210526316 |
| 500   | 973      | 4.80769230769231 |
| 500   | 974      |            4.625 |
| 500   | 975      |              4.6 |
| 500   | 976      | 4.80769230769231 |
| 500   | 978      |                5 |
| 500   | 979      |              4.8 |
+-------+----------+------------------+
9 rows in set (0.00 sec)

add the AVG(valuation) column from the second query (service_survey) to the result of the query by oper_survey with the data corresponding to the operators.

Answer 1

In a simple way:

SELECT 500 queue, operator, os.avg_os, ss.avg_ss
FROM ( SELECT operator,AVG(valuation) avg_os
       FROM oper_survey 
       WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
       GROUP BY operator ) os
JOIN ( SELECT operator, AVG(valuation) avg_ss
       FROM service_survey 
       WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
       GROUP BY operator ) ss USING (operator)

A more complicated option - some operator is missing in one of the tables. Then you need to collect a list from both tables, and bind counting subqueries to it.

SELECT 500 queue, operator, COALESCE(os.avg_os, 0) avg_os, COALESCE(ss.avg_ss, 0) avg_ss
FROM ( SELECT operator FROM oper_survey WHERE queue = '500'
       UNION 
       SELECT operator FROM service_survey WHERE queue = '500' ) op
LEFT JOIN ( SELECT operator,AVG(valuation) avg_os
            FROM oper_survey 
            WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
            GROUP BY operator ) os USING (operator)
LEFT JOIN ( SELECT operator, AVG(valuation) avg_ss
            FROM service_survey 
            WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
            GROUP BY operator ) ss USING (operator)