Cloud February 2016

Calculate correct percentage for each day

I have a table called tbl_answer. It save record for each user when they answer question for each day.

Here is the sample of the table.

id    date                      correct    user_id   question_id
1     2015-11-17 06:13:17       yes        11        1
2     2015-11-17 06:24:54       no         1         19
3     2015-11-18 02:09:13       no         129       88
4     2015-11-18 03:16:18       yes        12        98
5     2015-11-20 04:21:24       no         117       4
5     2015-11-20 04:29:24       yes        15        9
....
....
etc

There are a lot of records. In same day, many users are answers a lot of questions. So, some day record are repeat with another different value.

Now, I want to calculate total percent of correct answer for each day. And I don't want to show H:m:i.

So, I try with this query,

SELECT DATE(date) as date,
        ( ( select COUNT(case when correct='yes' then 1 END) )*100 / (count(DATE(date))) ) as percent 
FROM tbl_answer 
GROUP BY date 

After that, I got this result.

result

So, I need to sum those different percent for the same day.

For example, if there are 113 question was answer in 2015-11-24 and correct question is only 72. So, the correct percent of answer in 2015-11-24 is 63.71%.

So, I try query like this,

SELECT temp.date, SUM(temp.percent) AS total_percent 
FROM (  SELECT DATE(date) as date,
        ( ( select COUNT(case when correct='yes' then 1 END) )*100 / ( count(DATE(date))) ) as percent 
        FROM tbl_answer 
        GROUP BY date
    ) AS temp
GROUP BY temp.date

But, I get wrong result like this,

r        </div>
      </div>
    </div>
    
    <div class=

Answers


1000111 February 2016

There are some unnecessary group by in your query. I don't think that will result in correct output. So I adopt the following query.

Since you want date wise percentage of correct answer so the following query might suit your need.

Here's I've created the following table in order to test the query:

CREATE TABLE `tbl_answer` (
`ID`  int(11) NOT NULL AUTO_INCREMENT ,
`date`  timestamp NULL DEFAULT NULL ,
`correct`  char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
);

Query:

SELECT 
DATE_FORMAT(date,"%Y-%m-%d") `date`,
ROUND(SUM(CASE WHEN correct = 'yes' THEN 1 ELSE 0 END) / COUNT(*) * 100.0, 2) correctPercentage       
FROM tbl_answer 
GROUP BY DATE_FORMAT(date,"%Y-%m-%d")

Some sample data:

INSERT INTO `tbl_answer` VALUES ('1', '2016-02-08 00:00:00', 'yes');
INSERT INTO `tbl_answer` VALUES ('2', '2016-02-08 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('3', '2016-02-08 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('4', '2016-02-08 00:00:00', 'yes');
INSERT INTO `tbl_answer` VALUES ('5', '2016-02-08 00:00:00', 'yes');
INSERT INTO `tbl_answer` VALUES ('6', '2016-02-02 00:00:00', 'yes');
INSERT INTO `tbl_answer` VALUES ('7', '2016-02-02 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('8', '2016-02-02 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('9', '2016-02-02 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('10', '2016-02-08 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('11', '2016-02-08 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('12', '2016-02-07 00:00:00', 'no');
INSERT INTO `tbl_answer` VALUES ('13', '2016-02-07 00:00:00', 'yes');
INSERT INTO `tbl_answer` VALUES ('14', '2016-02-07 00:00:00', 'no');

How the table tbl_answer looks like with these data:

Post Status

Asked in February 2016
Viewed 3,400 times
Voted 13
Answered 1 times

Search




Leave an answer