Calculate correct percentage for each day

Question

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.

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,

I think (count(DATE(date))) is not working. I try to count how many questions in each day with (count(DATE(date))) but I'm not sure why its not work.

But now, it show only 7200.0. I already divide 7200/(count(DATE(date))). But I don't know why its not working.

Answer 1

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:

Output:

Output Explained:

• There are total 7 entries of 2016-02-08 date out of 3 are correct. (3/7 = 42.86)
• There are total 4 entries of 2016-02-02 date out of 1 is correct. (1/4 = 25.00)
• There are total 3 entries of 2016-02-07 date out of 1 is correct. (1/3 = 33.33)