php - How to count and sum a group of rows that share the same first letter -

php - How to count and sum a group of rows that share the same first letter -

i have info like:

studyid | participantid | question | problems 1 | 1 | a100 | 3 1 | 1 | a200 | 2 1 | 1 | a300 | 4 1 | 1 | b100 | 2 1 | 1 | b200 | 5 1 | 2 | a100 | 3 1 | 2 | a200 | 3 1 | 2 | a300 | 3 1 | 2 | b100 | 1 1 | 2 | b200 | 6

and want output like:

section | num questions| total problems | average problems | 3 | 18 | 6 b | 2 | 14 | 7

the code go in sql fiddle given below:

set @old_unique_checks=@@unique_checks, unique_checks=0; set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0; set @old_sql_mode=@@sql_mode, sql_mode='traditional,allow_invalid_dates'; create schema `interviewcodes` default character set utf8 ; utilize `interviewcodes` ; create table `interviewerlkup` ( `interviewerid` int(11) not null auto_increment, primary key (`interviewerid`)) engine = innodb default character set = utf8; create table `studylkup` ( `studyid` int(11) not null auto_increment, primary key (`studyid`)) engine = innodb default character set = utf8; create table `studyinterviewers` ( `studyid` int(11) not null, `interviewerid` int(11) not null, primary key (`studyid`, `interviewerid`), index `fk_studyinterviewers_interviewerlkup1_idx` (`interviewerid` asc), constraint `fk_studyinterviewers_interviewerlkup1` foreign key (`interviewerid`) references `interviewcodes`.`interviewerlkup` (`interviewerid`) on delete no action on update no action, constraint `fk_studyinterviewers_studylkup1` foreign key (`studyid`) references `interviewcodes`.`studylkup` (`studyid`) on delete no action on update no action) engine = innodb default character set = utf8; create table `participant` ( `participantid` int(11) not null auto_increment, `participantcaseid` varchar(45) not null, `studyid` int(11) not null, `interviewerid` int(11) not null, primary key (`participantid`), index `fk_participant_studyinterviewers1_idx` (`interviewerid` asc), constraint `fk_participant_studyinterviewers1` foreign key (`interviewerid`) references `interviewcodes`.`studyinterviewers` (`interviewerid`) on delete no action on update no action, constraint `fk_participant_studylkup1` foreign key (`studyid`) references `interviewcodes`.`studylkup` (`studyid`) on delete no action on update no action) engine = innodb default character set = utf8; create table `coderlkup` ( `coderid` int(11) not null auto_increment, primary key (`coderid`)) engine = innodb default character set = utf8; create table `studycoders` ( `studyid` int(11) not null, `coderid` int(11) not null, primary key (`studyid`, `coderid`), index `fk_studycoders_coderlkup1_idx` (`coderid` asc), constraint `fk_studycoders_coderlkup1` foreign key (`coderid`) references `interviewcodes`.`coderlkup` (`coderid`) on delete no action on update no action, constraint `fk_studycoders_studylkup1` foreign key (`studyid`) references `interviewcodes`.`studylkup` (`studyid`) on delete no action on update no action) engine = innodb default character set = utf8; create table `studyquestion` ( `studyquestionlabel` varchar(45) not null, `studyid` int(11) not null, primary key (`studyquestionlabel`, `studyid`), constraint `fk_studyquestion_studylkup` foreign key (`studyid`) references `interviewcodes`.`studylkup` (`studyid`) on delete no action on update no action) engine = innodb default character set = utf8; create table `codedata` ( `studyid` int(11) not null, `participantid` int(11) not null, `coderid` int(11) not null, `studyquestionlabel` varchar(45) not null, `totalscore` int(11) null default 0, index `fk_codedata_participant1_idx` (`participantid` asc), index `fk_codedata_studycoders1_idx` (`coderid` asc), index `fk_codedata_studyquestion1_idx` (`studyquestionlabel` asc), primary key (`studyid`, `participantid`, `coderid`, `studyquestionlabel`), constraint `fk_codedata_participant1` foreign key (`participantid`) references `interviewcodes`.`participant` (`participantid`) on delete no action on update no action, constraint `fk_codedata_studycoders1` foreign key (`coderid`) references `interviewcodes`.`studycoders` (`coderid`) on delete no action on update no action, constraint `fk_codedata_studylkup1` foreign key (`studyid`) references `interviewcodes`.`studylkup` (`studyid`) on delete no action on update no action, constraint `fk_codedata_studyquestion1` foreign key (`studyquestionlabel`) references `interviewcodes`.`studyquestion` (`studyquestionlabel`) on delete no action on update no action) engine = innodb default character set = utf8; set sql_mode=@old_sql_mode; set foreign_key_checks=@old_foreign_key_checks; set unique_checks=@old_unique_checks; insert `studylkup` (`studyid`) values ('3'); insert `interviewerlkup` (`interviewerid`) values ('1'); insert `interviewerlkup` (`interviewerid`) values ('2'); insert `coderlkup` (`coderid`) values ('1'); insert `coderlkup` (`coderid`) values ('2'); insert `studyinterviewers` (`studyid`, `interviewerid`) values ('3', '2'); insert `studyinterviewers` (`studyid`, `interviewerid`) values ('3', '1'); insert `studycoders` (`studyid`, `coderid`) values ('3', '2'); insert `studycoders` (`studyid`, `coderid`) values ('3', '1'); insert `participant` (`participantid`, `participantcaseid`, `studyid`, `interviewerid`) values ('1', 'ajw123', '3', '2'); insert `participant` (`participantid`, `participantcaseid`, `studyid`, `interviewerid`) values ('3', 'ajw125', '3', '1'); insert `studyquestion` (`studyquestionlabel`, `studyid`) values ('a100', '3'); insert `studyquestion` (`studyquestionlabel`, `studyid`) values ('a200', '3'); insert `studyquestion` (`studyquestionlabel`, `studyid`) values ('a300', '3'); insert `studyquestion` (`studyquestionlabel`, `studyid`) values ('b100', '3'); insert `studyquestion` (`studyquestionlabel`, `studyid`) values ('b200', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '1', '1', 'a100', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '1', '1', 'a200', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '1', '1', 'a300', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '1', '1', 'b100', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '1', '1', 'b200', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '3', '2', 'a100', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '3', '2', 'a200', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '3', '2', 'a300', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '3', '2', 'b100', '3'); insert `codedata` (`studyid`, `participantid`, `coderid`, `studyquestionlabel`, `totalscore`) values ('3', '3', '2', 'b200', '3');

update

i have tried:

print("<table align = 'center' border = '2'>"); print("<tr>"); print("<td align = 'center'>section</td>"); print("<td align = 'center'>total number of items</td>"); print("<td align = 'center'>total number of problems</td>"); print("<td align = 'center'>average number of problems</td>"); print("</tr>"); $sql1 = "select left(codedata.studyquestionlabel, 1) section, count(*) numquestions, sum(codedata.totalscore) totalproblems, avg(codedata.totalscore) avgproblems interviewcodes.codedata codedata (codedata.studyid = ".$studyid.") grouping left(codedata.studyquestionlabel, 1) order left(codedata.studyquestionlabel, 1) asc;"; // run query , result set $rs1 = $conn->query($sql1); // check if query1 wrong; if ($rs1 == false) { $errmsg = "wrong sql1: ".$sql1."error: ".$conn->error; trigger_error($errmsg, e_user_error); } else { while ($arr1 = $rs1->fetch_array(mysqli_assoc)) { print_r($arr1); print("<tr>"); print("<td align = 'center'>".$arr1['section']."</td>"); print("<td align = 'center'>".$arr1['numquestions']."</td>"); print("<td align = 'center'>".$arr1['totalproblems']."</td>"); print("<td align = 'center'>".$arr1['avgproblems']."</td>"); print("</tr>"); } } print('</table>');

i changed above code little bit , got next output.

section | num questions| total problems | average problems | 6 | 18 | 3 b | 4 | 14 | 3.5

i want instead:

section | num questions| total problems | average problems | 3 | 18 | 6 b | 2 | 14 | 7

it seems counting every record question starts w/ letter when want count distinct questions start letter a. how do that?

in sql, substr() starts counting @ 1 , not 0. in case, can utilize left() function , not have worry that:

select left(question, 1) section, count(*) numquestions, sum(problems) totalproblems codedata (studyid = ".$studyid.") grouping left(question, 1);

you can add together ration, seem want, or utilize avg() function:

select left(question, 1) section, count(*) numquestions, sum(problems) totalproblems, avg(problems) averageproblems codedata (studyid = ".$studyid.") grouping left(question, 1);

php mysql count group-by substring