I have this table with these two queries
but I can not find a way to show the two queries in one
The final result should look like this:
I have this table with these two queries
but I can not find a way to show the two queries in one
The final result should look like this:
I think Query 1 is self-explanatory. It is a question of adding up in a case only those reviewed.
MySQL 5.6 Schema Setup :
CREATE TABLE revisor
('id_proyecto' varchar(4), 'id_revisor' varchar(20), 'revisado' int)
;
INSERT INTO revisor
('id_proyecto', 'id_revisor', 'revisado')
VALUES
('b123', 'José', 0),
('b123', 'Pedro', 1),
('b123', 'Pablo', 0),
('a123', 'José', 0),
('a123', 'Pedro', 1),
('a123', 'Pablo', 1),
('a123', 'Pérez', 1);
Query 1 :
SELECT
id_proyecto, count(*) TotalRevisores,
sum(case when revisado = 1 then 1 else 0 end) as revisados
FROM
revisor
GROUP BY id_proyecto
Results :
| id_proyecto | TotalRevisores | revisados |
|-------------|----------------|-----------|
| a123 | 4 | 3 |
| b123 | 3 | 1 |
Another option would be: Using the union of your queries, and adding a column with zero value.
select id_proyecto,
sum(totalRevisores) totalRevisores,
sum(Revisados) Revisados
from ( select id_proyecto, count(*) totalRevisores, 0 Revisados
from Revisor group by id_proyecto
union select id_proyecto, 0 totalRevisores, count(*) Revisados
from Revisor where revisado = 1 group by id_proyecto )
tbaux group by 1