2 COUNT within a SELECT with LEFT JOIN

Question

I have two tables:

users:

id | nome | email

shipments:

id | id_envia | id_recebe

Every time a user sends a card (which goes by email) to another user, mark this table, and every time they receive also mark.

Doubt:

I want to make a SELECT on users and in the answer know how many times it was on id_send and how many times he was in id_recebe, I tried something like this:

SELECT usuarios.email, COUNT(envios.id_recebe) AS numeroRecebe, COUNT(envios.id_envia) AS numeroEnvia
FROM usuarios 

LEFT JOIN envios ON usuarios.id = envios.id_envia OR usuarios.id = envios.id_recebe

GROUP BY usuarios.email

The problem is that both the numero as to the numero Nvidia are returning the same value, the sum of the two... where I am missing?

Answer 1

The COUNT aggregation function counts the number of rows in the table, so COUNT(id_receive) or COUNT(id_send), will make no difference in this case, as the table has the same number of rows in both cases.

To get a different number you must make two different queries, where each one will have a number of rows corresponding to the number of receipts or shipments as needed. Then you can join the two queries with a JOIN and have the desired result.

Code to create tables and add some examples:

CREATE TABLE IF NOT EXISTS `usuarios` (
      `id` int NOT NULL,
      `nome` varchar(40) NOT NULL,
      `email` varchar(40) NOT NULL
);

CREATE TABLE IF NOT EXISTS `envios` (
      `id` int NOT NULL,
      `id_envia` int NOT NULL,
      `id_recebe` int NOT NULL
);

INSERT INTO usuarios (id, nome, email) VALUES (1, "José", "[email protected]");
INSERT INTO usuarios (id, nome, email) VALUES (2, "Ricardo", "[email protected]");
INSERT INTO usuarios (id, nome, email) VALUES (3, "Gabriel", "[email protected]");

INSERT INTO envios (id, id_envia, id_recebe) VALUES (1, 1, 2);
INSERT INTO envios (id, id_envia, id_recebe) VALUES (2, 1, 2);
INSERT INTO envios (id, id_envia, id_recebe) VALUES (3, 1, 3);
INSERT INTO envios (id, id_envia, id_recebe) VALUES (1, 2, 1);
INSERT INTO envios (id, id_envia, id_recebe) VALUES (1, 3, 1);

Query calculating different numbers of submissions and receipts:

SELECT recebidos.id, recebidos.numeroRecebe, enviados.numeroEnvia
FROM
    (SELECT usuarios.id, COUNT(envios.id_recebe) AS numeroRecebe
    FROM usuarios LEFT JOIN envios ON (usuarios.id = envios.id_recebe)
    GROUP BY usuarios.id)  recebidos
JOIN
    (SELECT usuarios.id, COUNT(envios.id_envia) AS numeroEnvia
    FROM usuarios LEFT JOIN envios ON (usuarios.id = envios.id_envia)
    GROUP BY usuarios.id) enviados
ON (recebidos.id = enviados.id)

You can test that it works.

Answer 2

I don’t know which database you are using, but follow an example in SQL Server:

    SELECT 
        A.email,
        SUM(B.id_envia) AS [Total de envios],
        SUM(B.id_recebe) AS [Total de recebidos]
    FROM usuarios  AS A
        INNER JOIN envios AS B
        ON A.id =   B.id
GROUP BY A.email

The columns you need to know the total are summed SUM() and group by email.

Answer 3

COUNT returns the number of rows, so returns the same value. How do you want to know how many times you sent and how many times you received so you should use the sum, as below:

select a.id, a.nome, 
sum( b.id_envia ) as enviados, 
sum( b.id_recebe ) as recebidos 
from usuarios a left join envios b
on a.id = b.id
where a.id = 1
group by a.id, a.nome