Re: How can I get a sum from specific fields in where clause?
Ah, I'm busted! lol.
As you see, I compressed the field names.
Translation: QID = QuestionID; SrvID = SurveyID; MX = MXInquiry; MA = MXAnswwer.
Multiple records having the same MX can exist in each survey. Of course multiple surveys exist in the table.
Answers also include a QuestionID.
A specific MX can exist in multiple surveys (all having the same QID).
A given question can include multiple MX but any specific MX can only exist for the same QID.
Sums are per survey (SrvID).
So, one sum for a given MX 'MX Sum' is the sum of all the numeric AnswerText values in one survey (SrvID) that have the same MX.
They of course would also have the same QID. All of the AnswerText values here will be numeric.
The query is: SELECT AnswerText, . . . FROM answers WHERE (QuestionID = 412 OR QuestionID = 17 OR QuestionID = 531 OR QuestionID = . . . )
(potentially) AND . . .
AND IF QuestionID = 531 then only the records in an 'MX Sum' that is >= GivenValue.
Sorry, I can't figure out a way to make this more clear.
If I can't do it I'll live without it but it would be quite valuable.
CREATE TABLE `answers` (
`RecordID` bigint unsigned NOT NULL AUTO_INCREMENT,
`QuestionaireID` smallint unsigned DEFAULT NULL,
`QuestionBankID` smallint unsigned DEFAULT NULL,
`QBSequenceRlID` smallint unsigned DEFAULT NULL,
`AttributionID` int unsigned DEFAULT NULL,
`AnswerLocationID` smallint DEFAULT NULL,
`AnswerEventID` smallint DEFAULT NULL,
`TouchPointID` smallint DEFAULT NULL,
`SurveyID` bigint unsigned DEFAULT NULL,
`SurveyorID` bigint unsigned DEFAULT NULL,
`SurveyorOrgID` int DEFAULT NULL,
`QTypeID` smallint unsigned DEFAULT NULL,
`QuestionID` int unsigned DEFAULT NULL,
`PredefinedAnswerID` int unsigned DEFAULT NULL,
`TDTRowID` int unsigned DEFAULT NULL,
`CXColumnID` int unsigned DEFAULT NULL,
`CXRowID` int unsigned DEFAULT NULL,
`LSTIdx` smallint unsigned DEFAULT NULL,
`MXInquiry` smallint DEFAULT NULL,
`MXAnswer` smallint DEFAULT NULL,
`ScaleID` smallint unsigned DEFAULT NULL,
`DMLevel` smallint unsigned DEFAULT NULL,
`DMItemIdx` smallint unsigned DEFAULT NULL,
`AnswerText` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`Score` float(10,2) unsigned DEFAULT NULL,
`ScoreSentiment` decimal(3,0) DEFAULT NULL COMMENT 'The calculated sentiment represented by the score',
`SentimentScore` decimal(3,2) DEFAULT NULL COMMENT 'The comment sentiment determined by Google.',
`SentimentMagnitude` decimal(3,2) DEFAULT NULL,
`CreatedByID` bigint DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`),
KEY `QuestionID` (`QuestionID`),
KEY `QuestionaireID` (`QuestionaireID`),
KEY `QTypeID` (`QTypeID`),
KEY `SurveyID` (`SurveyID`),
KEY `QuestionBankID` (`QuestionBankID`),
KEY `QBSequenceRlID` (`QBSequenceRlID`),
KEY `AttributionID` (`AttributionID`),
KEY `SurveyorID` (`SurveyorID`),
KEY `PredefinedAnswerID` (`PredefinedAnswerID`),
CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`QuestionaireID`) REFERENCES `questionaires` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_2` FOREIGN KEY (`QuestionBankID`) REFERENCES `question_banks` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_3` FOREIGN KEY (`QuestionID`) REFERENCES `questions` (`RecordID`),
CONSTRAINT `answers_ibfk_4` FOREIGN KEY (`QBSequenceRlID`) REFERENCES `qb_sequences_rl` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_5` FOREIGN KEY (`AttributionID`) REFERENCES `attributions` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_6` FOREIGN KEY (`SurveyID`) REFERENCES `surveys` (`RecordID`),
CONSTRAINT `answers_ibfk_7` FOREIGN KEY (`SurveyorID`) REFERENCES `person` (`RecordID`) ON DELETE SET NULL,
CONSTRAINT `answers_ibfk_8` FOREIGN KEY (`PredefinedAnswerID`) REFERENCES `q_predefined_answers` (`RecordID`)
) ENGINE=InnoDB AUTO_INCREMENT=1340 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;