Tally

Question

I have a table called cad_faltas, he owns the fields:

id int(11)
id_cad_alunos int(11) 
falta varchar(8)
idcurso int(11)

The field falta receives the data MISSING and/or PRESENT. The question is, is there any possibility to do a count of records that are as "MISSING" for each idcurso and also know the percentage of records with this information?

Example:

For the course of "id" 2 there are three records like "ABSENT" which are 50% of all records for this "id" 2.

Answer 1

As you need both the amount of records with absent as gifts, a clause where becomes unviable. What you can do is calculate the sum of values, to define the number of absentees, setting the value 1 when the record is absent or 0 when it is present. The sum will be the amount of missing records. To calculate the share that this value represents of the total, you can calculate the average value, also defining the value 1 when it is absent or 0 when it is present. Take an example:

select 
  sum(case when falta = "AUSENTE" then 1.0 else 0.0 end) as ausentes, 
  100*avg(case when falta = "AUSENTE" then 1.0 else 0.0 end) as parcela,
  idcurso
from cad_faltas 
group by idcurso;

See working on Sqlfiddle.

Answer 2

If what you need is the amount of absences and percentage per course, follows a way of doing:

SELECT
  a.idcurso,
  nmfaltas,
  (nmfaltas * 100) / COUNT(*) percfaltas
FROM
  cad_faltas a
JOIN
  (SELECT idcurso, COUNT(*) nmfaltas FROM cad_faltas where falta = 'AUSENTE' GROUP BY idcurso) b on a.idcurso = b.idcurso
GROUP BY idcurso

Sqlfiddle