Query data in two tables and add one of the columns

Question

I have two tables need to make a SELECT in both at the same time, basically it will work like this:

Note that the result returns the column nome of Tabela A and sum of column media where the ids are equal. And also the names where there is no record in the Tabela B are displayed with the value 0.

I searched on the site something similar and did not find, how can I do it?

Answer 1

To get the expected result you need to make a JOIN between tables. Also, you need to use aggregation functions with GROUP BY to be able to group and sum the values.

Your query would look like this:

SELECT a.nome, 
       COALESCE(SUM(b.media), 0) AS media
  FROM tabela_a a
  LEFT JOIN tabela_b b ON b.id_usuario = a.id
 GROUP BY a.nome;

One remark: in the example exposed by you JOSÉ had the added values, but MARIA had as a result the average of the values, then got a little confused. If you want to calculate the average and not the sum, you should use the function AVG instead of SUM.

SELECT a.nome, 
       COALESCE(AVG(b.media), 0) AS media
  FROM tabela_a a
  LEFT JOIN tabela_b b ON b.id_usuario = a.id
 GROUP BY a.nome;

An example HERE for execution in Sqlfiddle.

---

JOIN, INNER or LEFT?

How the information is distributed by two tables it is necessary to relate them through the existing connection between the keys present in them (primary and foreign).

We get this from the use of JOIN. But there is an important difference between using INNER JOIN and LEFT JOIN.

With INNER JOIN the result of query is limited to the records that meet the full connection conditions in the two tables, i.e., for each record of table A we have to find one or more corresponding records in table B.

If we used INNER JOIN in the example the result of query would bring only the records of JOSEPH AND MARY, for JOÃO and ANA found no related records in table B.

Already with LEFT JOIN the result of query corresponds to the total of records originated from the table found in left side of the relationship, in our case, table A. There is no need for the existence of related records in table B. Therefore, with the use of LEFT JOIN we could bring all the names in the result, regardless of information in table B.

GROUP BY

Through the GROUP BY we group records of query and we can apply the aggregation functions on the fields to perform operations such as counting (COUNT), averaging (AVG), summing up (SUM), among others.

COALESCE

How will we have no information about JOÃO and ANA in table B the result of the application of an aggregation function for your records, be the average (AVG) or the sum (SUM), shall be void (NULL).

The function COALESCE allows the NULL is replaced by another value, 0 in the examples. It evaluates all the reported parameters and always returns the first other than null.

• COALESCE(NULL, 1, 3) returns the second parameter, the value 1.
• COALESCE(2, 1) returns the first parameter, the value 2.
• COALESCE(AVG(b.media), 0) returns the average if different from NULL, but 0.

In this particular case we could also use the function IFNULL (which only accepts 2 parameters, while COALESCE can evaluate several parameters) with the same result of COALESCE.

Documentation

Mysql JOIN documentation

Mysql GROUP BY documentation

Documentation Mysql COALESCE

Mysql IFNULL documentation

More information and examples

How to display data from a foreign key table in my main table?

Category query in Mysql

Query in SQL Server