‘IF’ condition in ‘SELECT’ statement

MySQL IF() takes three expressions and if the first expression is true, not zero and not NULL, it returns the second expression. Otherwise, it returns the third expression.

Depending on the context in which it is used, it returns either numeric or string value.

Syntax:

IF(expression ,expr_true, expr_false);

Parameters:

Name	Description	Return Type
expression	An expression.
expr_true	Returns when the condition is TRUE.	a string when expr_true is a string, a floating-point value when expr _true is a floating-point value and an integer when expr _true is an integer.
expr_false	Returns when the condition is FALSE.	a string when expr_false is a string, a floating-point value when expr _false is a floating-point value and an integer when expr _false is an integer.

Example:

SELECT  sess_id, SUM(if(`true_ans` = `your_ans`, 1,0)) as rightAnswer, SUM(if(`true_ans` != `your_ans`, 1,0)) as wrongAnswer, count(`que_des`) as totalQuestions, exam_date as examDate FROM `mst_useranswer`  GROUP BY sess_id

Table structure:

CREATE TABLE IF NOT EXISTS `mst_useranswer` (
  `sess_id` varchar(80) DEFAULT NULL,
  `test_id` int(11) DEFAULT NULL,
  `que_des` varchar(200) DEFAULT NULL,
  `ans1` varchar(50) DEFAULT NULL,
  `ans2` varchar(50) DEFAULT NULL,
  `ans3` varchar(50) DEFAULT NULL,
  `ans4` varchar(50) DEFAULT NULL,
  `true_ans` int(11) DEFAULT NULL,
  `your_ans` int(11) DEFAULT NULL,
  `exam_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert data:

INSERT INTO `mst_useranswer` (`sess_id`, `test_id`, `que_des`, `ans1`, `ans2`, `ans3`, `ans4`, `true_ans`, `your_ans`, `exam_date`) VALUES
('shashank1', 0, '249', '1', '2', '3', '4', 2, 2, '2017-05-09 12:40:18'),
('shashank1', 0, '229', '1', '2', '3', '4', 3, 1, '2017-05-09 12:40:18'),
('shashank1', 0, '136', '1', '2', '3', '4', 4, 3, '2017-05-09 12:40:18'),
('shashank1', 0, '231', '1', '2', '3', '4', 2, 3, '2017-05-09 12:40:18'),
('shashank1', 0, '193', '1', '2', '3', '4', 1, 4, '2017-05-09 12:40:18'),
('shashank1', 0, '139', '1', '2', '3', '4', 3, 3, '2017-05-09 12:40:18'),

('shashank1', 0, '190', '1', '2', '3', '4', 2, 3, '2017-05-09 12:40:18');

Output: