Как посчитать и сложить группу строк, которые имеют одну и ту же первую букву

Question

Как посчитать и сложить группу строк, которые имеют одну и ту же первую букву

у меня есть данные как:

    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

и я хочу вывод, как:

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

Код, который будет идти в скрипте sql, приведен ниже:

    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 ;
USE `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 INTO `studylkup` (`StudyID`) VALUES ('3');

INSERT INTO `interviewerlkup` (`InterviewerID`) VALUES ('1');
INSERT INTO `interviewerlkup` (`InterviewerID`) VALUES ('2');

INSERT INTO `coderlkup` (`CoderID`) VALUES ('1');
INSERT INTO `coderlkup` (`CoderID`) VALUES ('2');INSERT INTO `studyinterviewers` (`StudyID`, `InterviewerID`) VALUES ('3', '2');
INSERT INTO `studyinterviewers` (`StudyID`, `InterviewerID`) VALUES ('3', '1');INSERT INTO `studycoders` (`StudyID`, `CoderID`) VALUES ('3', '2');
INSERT INTO `studycoders` (`StudyID`, `CoderID`) VALUES ('3', '1');

INSERT INTO `participant` (`ParticipantID`, `ParticipantCaseID`, `StudyID`, `InterviewerID`) VALUES ('1', 'AJW123', '3', '2');
INSERT INTO `participant` (`ParticipantID`, `ParticipantCaseID`, `StudyID`, `InterviewerID`) VALUES ('3', 'AJW125', '3', '1');

INSERT INTO `studyquestion` (`StudyQuestionLabel`, `StudyID`) VALUES ('A100', '3');
INSERT INTO `studyquestion` (`StudyQuestionLabel`, `StudyID`) VALUES ('A200', '3');
INSERT INTO `studyquestion` (`StudyQuestionLabel`, `StudyID`) VALUES ('A300', '3');
INSERT INTO `studyquestion` (`StudyQuestionLabel`, `StudyID`) VALUES ('B100', '3');
INSERT INTO `studyquestion` (`StudyQuestionLabel`, `StudyID`) VALUES ('B200', '3');INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '1', '1', 'A100', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '1', '1', 'A200', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '1', '1', 'A300', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '1', '1', 'B100', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '1', '1', 'B200', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '3', '2', 'A100', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '3', '2', 'A200', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '3', '2', 'A300', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '3', '2', 'B100', '3');
INSERT INTO `codedata` (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`, `TotalScore`) VALUES ('3', '3', '2', 'B200', '3');

ОБНОВИТЬ

я пытался:

        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) as Section,
COUNT(*) as NumQuestions,
SUM(codedata.TotalScore) as TotalProblems,
AVG(codedata.TotalScore) as AvgProblems
FROM interviewcodes.codedata codedata
WHERE (codedata.StudyID = ".$StudyID.")
GROUP BY LEFT(codedata.StudyQuestionLabel, 1)
ORDER BY LEFT(codedata.StudyQuestionLabel, 1) ASC;";

// run query and get result set
$rs1 = $conn->query($sql1);

// check if query1 is 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>');

Я немного изменил приведенный выше код и получил следующий вывод.

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

я хочу это вместо этого:

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

Кажется, он считает каждую запись с вопросом, начинающимся с буквы А, когда я действительно хочу, чтобы он подсчитал различные вопросы, начинающиеся с буквы А. Как мне это сделать?

0

count group-by mysql php substring

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

В SQL substr() начинает считать с 1, а не с 0. В любом случае вы можете использовать left() функционировать и не беспокоиться об этом:

SELECT LEFT(Question, 1) as section, COUNT(*) as numQuestions, SUM(Problems) as totalProblems
FROM codedata
WHERE (StudyID = ".$StudyID.")
GROUP BY LEFT(Question, 1);

Вы также можете добавить рацион, который вам, кажется, тоже нужен, или использовать avg() функция:

SELECT LEFT(Question, 1) as section, COUNT(*) as numQuestions, SUM(Problems) as totalProblems,
AVG(Problems) as averageProblems
FROM codedata
WHERE (StudyID = ".$StudyID.")
GROUP BY LEFT(Question, 1);

1