Query SQL Doubt - Query using two tables

Question

I’m having doubts about how to query. The problem is the following:

• I have two tables similar to the ones described below (I don’t have exactly how they were, but are next to the representation below):

Table 1 - Countries

| País  | Empresa  |

|País A| Empresa 1|

|País A| Empresa 2|

|País A| Empresa 3|

|País B| Empresa 4|

|País B| Empresa 5|

|País C| Empresa 6|

Table 2 - Sales

| Código | Vendedor | Comprador | Valor |

| 1      | Empresa 1| Empresa 5 | 100   |

| 2      | Empresa 1| Empresa 4 | 20    |

| 3      | Empresa 6| Empresa 2 | 120   |

| 4      | Empresa 5| Empresa 6 | 10    |

| 5      | Empresa 6| Empresa 1 | 200   |

| 6      | Empresa 4| Empresa 2 | 60    |

In table 1 there is no key Primary. In table 2 the column "code" is key Primary. All table columns are varchar, except the columns "code" and "value".

The result should be a table with the purchase and sale values of each country. Of the type below:

Table 3 - Result

| País | Vendas | Compras |

| País A | 120 | 280 |

| País B | 70 | 120 |

| País C | 220 | 10 |

I tried to write a query that arrived at this result, but I couldn’t. In the querys that I did, the closest I could was the list of countries, but the sum was not correct. Could someone help me with this problem? Please.

Answer 1

One way you have to do it is to realize subqueries to calculate the purchase and sale and then use a GROUP together with the SUM in the result:

SELECT x.pais,
       SUM(x.vendas) AS vendas,
       SUM(x.compras) AS compras
FROM (
  SELECT p.pais,
         (SELECT SUM(v.valor)
            FROM vendas v
           WHERE v.vendedor = p.empresa) AS vendas,
         (SELECT SUM(v.valor)
            FROM vendas v
           WHERE v.comprador = p.empresa) AS compras
    FROM paises p
) x
GROUP BY x.pais;

Another way is to realize a CROSS JOIN and use a CASE to check whether the sale in question has a link with the country:

SELECT p.pais,
       SUM(CASE v.vendedor WHEN p.empresa THEN v.valor ELSE 0 END) AS vendas,
       SUM(CASE v.comprador WHEN p.empresa THEN v.valor ELSE 0 END) AS compras
  FROM paises p
 CROSS JOIN vendas v
GROUP BY p.pais;

And there is still a third way. You can realize LEFT JOIN with the table vendas binding by vendedor and also by comprador and thus group the information together with SUM:

SELECT p.pais,
       SUM(v1.valor) AS vendas,
       SUM(v2.valor) AS compras
  FROM paises p
  LEFT JOIN vendas v1 ON v1.vendedor = p.empresa
  LEFT JOIN vendas v2 ON v2.comprador = p.empresa
 GROUP BY p.pais

The implementation plan needs to be checked to see which solution costs the least for the database and best applies to the database scenario.

Answer 2

If I understood your problem correctly, you would need to make two queries to get this result. The first query would be to assemble the list of companies+parents with purchases and sales. It would look like this:

select P.Pais, P.Empresa,
(Select sum(valor) from Vendas where vendedor = P.Empresa) as vendas,
(Select sum(valor) from Vendas where comprador = P.Empresa) as compras
from Paises as P
GROUP BY P.Pais, P.Empresa

On this result, you could do the grouping to assemble the table by parents. It would look like this:

Select Pais, sum(vendas) as vendas, sum(compras) as compras
from (
select P.Pais, P.Empresa,
(Select sum(valor) from Vendas where vendedor = P.Empresa) as vendas,
(Select sum(valor) from Vendas where comprador = P.Empresa) as compras
from Paises as P
GROUP BY P.Pais, P.Empresa
) tabela
GROUP BY Pais

Answer 3

You can make a sub query used to know the purchase and sale values by parents/company, and then group by parents.

select 
    t.pais, sum(t.vendas) vendas, sum(t.compras) compras
from  (

    select 
        p.pais pais, 
        p.empresa empresa,
        (select sum(v.valor) from vendas v where v.vendedor = p.empresa ) vendas,
        (select sum(c.valor) from vendas c where c.comprador = p.empresa ) compras
    from paises p 
) t
group by t.pais

See an example here: https://www.db-fiddle.com/f/8buC9rz8gXQtUCX8kw9jPV/3