MYSQL count based on conditions in same column l have the following data sample

CREATE TABLE IF NOT EXISTS `tbl_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(10) NOT NULL,
  `subject_id` int(5) NOT NULL,
  `score` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;


INSERT INTO `tbl_result` (`id`, `student_id`, `subject_id`, `score`) VALUES
(3, 'R135722F', 7112, 43),
(4, 'R135722F', 6118, 55),
(5, 'R135722F', 2076, 45),
(6, 'R135722F', 4402, 76),
(7, 'R135722F', 2234, 34),
(8, 'R134567Y', 6118, 65),
(9, 'R134567Y', 2076, 87),
(10, 'R134567Y', 4402, 43),
(11, 'R134567Y', 2234, 65),
(12, 'R134567Y', 1111, 34),
(13, 'R134567Y', 3454, 65),
(14, 'R134567Y', 3456, 87),
(15, 'R137634H', 7112, 98),
(16, 'R137634H', 6118, 54),
(17, 'R137634H', 2076, 45),
(18, 'R137634H', 4402, 33),
(19, 'R137634H', 2234, 65),
(20, 'R137634H', 2234, 65),
(21, 'R137634H', 1111, 12),
(22, 'R137634H', 3454, 54);


How do l retrieve the following how many students passed the following subjects ie

Those who passed 3 or subjects including subject code 7112 , 6118
Those who passed 4 or more including subject code 1111 , 3454 

l have tried to use the in function but it  did not produce desired result

    SELECT COUNT( * ) 
FROM  `tbl_result` 
WHERE  `subject_id` 
IN (
 '7112',  '6118'
)


ANd the following but failing . also tried this one

SELECT * 
FROM  `tbl_result` 
WHERE  `subject_id` =  '7112'
OR  `subject_id` =  '6118'
AND  `score` &gt;49
GROUP BY  `student_id
As far as you don't show us your desired result let me show some approaches to your question.

Your first example:

SELECT COUNT(student_id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118');


give a result of 5, it doesn't matter if you use COUNT(*) or COUNT(student_id).

| count |
|:-----:|
|   5   |


If you want to know which students then you can use GROUP BY student_id, but then you should add student_id to the SELECT statement:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
GROUP BY subject_id;


Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   2   |


For the second one use the same syntax adding more conditions:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score &gt; 49
GROUP BY subject_id;


Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   1   |

SELECT COUNT(*) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score &gt; 49;

| count |
|:-----:|
|   4   |


Rextester here