Problem in SQL search

Question

I am doing an exercise where, for each name of a discipline, I need to return the number of students approved and failed. When I search for the approved and failed students separately, I get the expected result:

SELECT D.nome, COUNT (H2.conceito) as reprovado
FROM disciplina D , historico H2
WHERE D.nome = 'Calculo'  and H2.conceito ='reprovado'
GROUP BY D.nome, H2.conceito

SELECT D.nome, COUNT (H.conceito) as aprovado
FROM disciplina D , historico H
WHERE D.nome = 'Calculo' and H.conceito = 'aprovado'
GROUP BY D.nome, H.conceito

The correct result would be 2 students approved and 2 failed, but when I gather the codes to do the search together, the result comes out as 4 students approved and 4 failed

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 tried to use concept as DISTINCT, but it didn’t work either.

Answer 1

One way to do this control is, instead of two joins with the table of historical, bring the approved/disapproved control of the where, adding up (sum) in accordance with the concept - control made via CASE.

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 , historico H
WHERE D.nome = 'calculo'
GROUP BY D.nome

I created a snipet with a test for example

Answer 2

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