In this solution I tried to offer 3 resolution options, being them:
- Solution Proposition 01 - With GROUP BY
- Solution Proposition 02 - With Sub-Consumption
- Proposed Solution 03 - With CASE WHEN
The solutions have been tested in SQL Fiddle. I used as initial base the data model provided by @rLinhares in his reply with small adjustments.
At the end of the answer, I will show the problem of including more than one table after the from
and not performing the junction join
thus exemplified in the Query PROBLEMA
with the explanation shortly after the statement of the result of this query
.
Solution Proposition 01 - With GROUP BY
Since the question states the use of the clause GROUP BY
as quoted below:
SELECT D.nome, COUNT (H.conceito) as aprovado, COUNT (H2.conceito) as reprovado
FROM disciplina D , historico H, historico H2
WHERE D.nome = 'Calculo' and H.conceito = 'aprovado' and H2.conceito ='reprovado'
GROUP BY D.nome, H.conceito, H2.conceito`
I present the possibility of following solution:
-- Solução Proposta 01 - Com GROUP BY
SELECT d.nome, h.conceito, Count(*) as Quantidade
FROM disciplina d
Left join historico h on d.id_disciplina = h.id_disciplina
WHERE d.nome = 'calculo'
group by d.id_disciplina, h.conceito, d.nome
order by d.id_disciplina, h.conceito, d.nome
Upshot:
| nome | conceito | quantidade |
|---------|-----------|------------|
| calculo | aprovado | 2 |
| calculo | reprovado | 3 |
Solution Proposition 02 - With Sub-Consumption
This solution can also be used to solve, although it is not widely used for this purpose.
-- Solução Proposta 02 - Com SubConsulta
SELECT d.Nome,
(Select count(*) from historico h where h.id_disciplina = d.id_disciplina and h.conceito = 'aprovado') as aprovado,
(Select count(*) from historico h where h.id_disciplina = d.id_disciplina and h.conceito = 'reprovado') as reprovado
FROM disciplina d
WHERE d.nome = 'calculo'
Upshot:
| nome | aprovado | reprovado |
|---------|----------|-----------|
| calculo | 2 | 3 |
Proposed Solution 03 - With CASE WHEN
How are you using the SUM
, is also necessary to group by
.
-- Solução Proposta 03 - Com CASE WHEN
SELECT d.nome,
SUM(CASE WHEN H.conceito = 'aprovado' THEN 1 ELSE 0 END) as aprovado,
SUM(CASE WHEN H.conceito = 'reprovado' THEN 1 ELSE 0 END) as reprovado
FROM disciplina d
Left join historico h on d.id_disciplina = h.id_disciplina
WHERE d.nome = 'calculo'
group by d.nome
Upshot:
| nome | aprovado | reprovado |
|---------|----------|-----------|
| calculo | 2 | 3 |
The problem that occurs if you do not use the join
correctly
Query PROBLEM:
-- Caso sem o join
SELECT D.nome, COUNT (H.conceito) as aprovado, COUNT (H2.conceito) as reprovado
FROM disciplina D , historico H, historico H2
WHERE D.nome = 'calculo' and H.conceito = 'aprovado' and H2.conceito ='reprovado'
GROUP BY D.nome, H.conceito, H2.conceito
Upshot:
| nome | aprovado | reprovado |
|---------|----------|-----------|
| calculo | 12 | 12 |
In that situation, if you do not use the JUNCTION a relational algebra of all records meeting the WHERE clause.
- subjects with name = 'calculation': 1 Record
H.conceito = 'aprovado'
: 4 Records
H2.conceito ='reprovado'
: 3 Records
Multiply everything because you don’t have the join
: 1 x 4 x 3 = 12 records
What is the structure of the tables involved? Can you edit the question and include the table creates and some Insert so that we can simulate? At first I can say that when you have two tables, you need to do
join
to unite the tables involved.– Clarck Maciel
This is happening because you are grouping your data. Try using CASE WHEN.
– Lucas Galvao