mysql - Calculate correct percentage for each day -

i have table called tbl_answer. save record each user when answer question each day.

here sample of 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 lot of records. in same day, many users answers lot of questions. so, day record repeat different value.

now, want calculate total percent of correct answer each day. , don't want show h:m:i.

so, try query,

select date(date) date,         ( ( select count(case when correct='yes' 1 end) )*100 / (count(date(date))) ) percent  tbl_answer  group date  

after that, got result.

so, need sum different percent same day.

for example, if there 113 question answer in 2015-11-24 , correct question 72. so, correct percent of answer in 2015-11-24 63.71%.

so, try query this,

select temp.date, sum(temp.percent) total_percent  (  select date(date) date,         ( ( select count(case when correct='yes' 1 end) )*100 / ( count(date(date))) ) percent          tbl_answer          group date     ) temp group temp.date 

but, wrong result this,

i think (count(date(date))) not working. try count how many questions in each day (count(date(date))) i'm not sure why not work.

but now, show 7200.0. divide 7200/(count(date(date))). don't know why not working.

there unnecessary group by in query. don't think result in correct output. adopt following query.

since want date wise percentage of correct answer following query might suit need.

here's i've created following table in order test 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' 1 else 0 end) / count(*) * 100.0, 2) correctpercentage        tbl_answer  group date_format(date,"%y-%m-%d") 

some sample data:

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

how table tbl_answer looks these data:

output:

output explained:

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