# Developers Planet

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
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
GROUP BY date
) AS temp
GROUP BY temp.date
``````

But, I get wrong result like this,

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
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: