0

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` >49
GROUP BY  `student_id
1
  • You should add which is your expected result, as your questions could have more than one possible interpretation. See answers below. – joanolo Jun 4 '17 at 9:11
0

There is more than one possible interpretation of your questions, and no indication of which is the "passing score". I assumed it is 50.

First question

First interpretation

 -- Those who passed 3 or subjects including subject code 7112 , 6118
 -- My interpretation:
 --    student passed 3 or more subjects
 --    student passed *both* subject 7112 and 6118
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50  -- I assume this is how you check student passed
         GROUP BY student_id
         HAVING count(*) >= 3
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 7112 AND score >= 50
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 6118 AND score >= 50
     ) ;

 | student_id |
 | :--------- |
 | R137634H   |
 

Second interpretation

 -- Those who passed 3 or subjects including subject code 7112 , 6118
 -- Second interpretation:
 --    student passed 3 or more subjects
 --    student passed *either* subject 7112 or 6118
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 3
     )
     AND 
     student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (7112, 6118) AND score >= 50
     ) ;

 | student_id |
 | :--------- |
 | R134567Y   |
 | R137634H   |
 

Second question

First interpretation

 -- Those who passed 4 or more including subject code 1111 , 3454
 --  First interpretation
 --    student passed 4 or more subjects
 --    student passed *both* subject 1111 and 3454
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 1111 AND score >= 50
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 3454 AND score >= 50
     ) ;

 | student_id |
 | :--------- |
 

Second interpretation

 -- Those who passed 4 or more including subject code 1111 , 3454
 --  2nd interpretation
 --    student passed 4 or more subjects
 --    student passed *either* subject 1111 or 3454
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (1111, 3454) AND score >= 50
     ) ;

 | student_id |
 | :--------- |
 | R134567Y   |
 | R137634H   |
 

Just counts

If you don't want the list of all the students, change in all the queries SELECT DISTINCT student_id by SELECT count(DISTINCT student_id):

 SELECT 
      count(DISTINCT student_id) AS my_count
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (1111, 3454) AND score >= 50
     ) ;

 | my_count |
 | -------: |
 |        2 |
 

dbfiddle here

1

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 > 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 > 49;

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

Rextester here

1
  • sorry for late response l wanted likeThose who passed 3 or more subjects including subject code 7112 , 6118 . A pass starts at 50 and ends at 100 for the score – Billy Watsy Jun 5 '17 at 11:07
0

With "Those who passed 3 or subjects including subject code 7112 , 6118" I assume you mean that they should have passed both 7112 and 6118 together with at least 1 other subject, correct? The students who have passed both 7112 , 6118 is:

select student_id 
from tbl_result 
where score > 49 
  and subject_id in (7112 , 6118) 
group by student_id 
having count(distinct subject_id = 2); -- means that both subjects are passed

Student who have passed at least subject beside 7112 , 6118 is

select student_id 
from tbl_result 
where score > 49 
  and subject_id not in (7112 , 6118) 
group by student_id 
having count(distinct subject_id > 0);

The final one can be simplified with:

select distinct student_id      
from tbl_result      
where score > 49        
  and subject_id not in (7112 , 6118);

By taking the intersection between these two sets the final goal is achieved. Unfortunate intersect does not seem to be implemented in mysql. We could handle this with using the EXISTS predicate, but I'll demonstrate another possibility:

select student_id
from tbl_result
where score > 49 
group by student_id 
having count(distinct case when subject_id in ('7112',  '6118') 
                      then subject_id end) = 2 
   and count(distinct case when subject_id not in ('7112', '6118')
                      then subject_id end) > 0;
1
  • your assumption is correct thanks. its what l have been looking for thanks – Billy Watsy Jun 5 '17 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.