Calculate percentage between two dates MYSQL

Navigation
1

Good morning!

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

This is the "questionnaire" table:

Itrieditasfollows:

$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 get by the URL and works fine, however the percentage that returns is wrong, it is only correct when I choose all the information in the database, but if it is a specific date everything is wrong ...

p>     
asked by anonymous 27.06.2018 / 13:57

3 answers

2

Try something more or less 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 sure try the top 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 have put the correct names

    
27.06.2018 / 14:38
3

The percentage will be determined by the number of items of a given rating divided by the total of items in all ratings, but the other filters - in their case apparently only dates - must be the same in the source of the total and the items by classification .

That is: 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 in the join, because the Total will be unique applied to all the regitros. ** The date in the filter is in the format YYYYMMDD

Note that the filter is the same in both queries.

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

I hope I have helped.

    
27.06.2018 / 15:00
0

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

    
27.06.2018 / 14:07
Count number of times letters repeat in a text Only allow to select two days ahead