2 COUNT within a SELECT with LEFT JOIN

The COUNT aggregate function counts the number of rows in the table, so COUNT (id_recebe) or COUNT (en_id) will not make any difference in this case, since 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 what works.

I do not know which database you're using, but here's 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 total are summed SUM() and grouped by email .

The COUNT returns the number of rows, so it returns the same value. As you want to know how many times you've sent and how many times you've received then you should use 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