Calculate percentage between two MYSQL dates

Question

Good morning!

I wanted to calculate the percentage of how many times a classification appears between two dates, the classifications are distributed in "red", "yellow" and "green".

This is the table "questionario":

I tried it this way:

$percentagem_verm=mysqli_query($link,"SELECT COUNT(q.pergunta_id) / t.total * 100 as perc, classificacao FROM questionario q,
( SELECT COUNT(*) AS total FROM questionario) t
WHERE q.classificacao = 'vermelho' AND data BETWEEN '".$_POST["datainicial"]."' AND '".$_POST["datafinal"]."'
 GROUP BY q.classificacao "));

The dates I picked up by the URL and it works right, but the percentage that returns is wrong, it is only correct when I choose all the information from the database, but if it is a specific date it is all wrong...

Answer 1

The percentage will be determined by the number of items of a certain classification divided by the total of items of all classifications, but the other filters - in your case apparently are only dates - need to be the same in the source of the total and items by classification.

That is to say: TotalClassificacaoPeriodo / TotalPeriodo.

The SELECT would look something like this:

SELECT q.classificacao, (COUNT(*) / t.Total) * 100 AS Percentual
FROM Questionario q,
  (SELECT COUNT(*) AS Total 
   FROM Questionario aux 
   WHERE aux.data >= '20180201' AND
         aux.data <= '20180228') t
WHERE q.data >= '20180201' AND
    q.data <= '20180228'
GROUP BY q.classificacao

^{* I used the subselect on the Join, because the Total shall be applied only for all regions.
** Filter date is in format YYYYMMDD}

Note that the filter is the same in both queries.

You can check in this scenario that I created in SQL Fiddle to demonstrate this logic working

I hope I’ve helped.

Answer 2

Try something like this

select ((count(if((classificacao = 'vermelho'), 1, 0)) / count(classificacao)) * 100) as perc
      ,classificacao
  from questionario
 where data BETWEEN '".$_POST["datainicial"]."' AND '".$_POST["datafinal"]."'
 group by classificacao

If not for sure the top try without the group by

select ((count(if((classificacao = 'vermelho'), 1, 0)) / count(classificacao)) * 100) as perc
  from questionario
 where data BETWEEN '".$_POST["datainicial"]."' AND '".$_POST["datafinal"]."'

EDITED

Sorry, I work more with Firebird :D

In mysql do so

select ((count(CASE WHEN classificacao like 'vermelho' THEN 1 END) / count(classificacao)) * 100) as perc
  from questionario
 where data BETWEEN '".$_POST["datainicial"]."' AND '".$_POST["datafinal"]."

Note: Just check if I put the correct names

Answer 3

You are calculating the total 'SELECT Count(*) as total from questionario' always with all the records of the table while calculating the percentages by filtering the dates with the 'between'. To be correct you must calculate the total sum based on the same conditions.