CODEWITHSUNDEEP
mysqllaravel
Raghu
Raghu

Reputation: 55

How to get distinct count of a column with respective to distinct values of another column in same table?

I have marks table with student id, class, section, subject id, total marks and isPassed status (1=pass, 0=fail). I want total number of student in a class or section having isPassed status 1 in all subjects. My query looks like this:

 $pass = Marks::select('student_id')
                    ->where('class_room_id', '=', 10)
                    ->where('section_id', '=', 1)
                    ->where('isPassed', '=', 1)
                    ->distinct()
                    ->get();
 $passed = count($pass);

The Table looks like:

student_id  subject_id  isPassed
  1001           100           1
  1001           101           0
  1001           102           1
  1001           103           1
  1001           104           1

  1002           100           1
  1002           101           1
  1002           102           1
  1002           103           1
  1002           104           1

Upvotes: 1

Views: 80

Answers (2)

Ildar Akhmetov
Ildar Akhmetov

Reputation: 1431

Your SQL query should be like this:

SELECT COUNT(student_id)
FROM marks
WHERE class_room_id = 10
AND student_id NOT IN (SELECT student_id FROM marks WHERE class_room_id = 10 AND isPassed = 0)

A nested query SELECT student_id FROM marks WHERE class_room_id = 10 AND isPassed = 0 selects all students in a given class who have at least one test with isPassed = 0. These are the students you DO NOT need.

UPDATE

Okay, after having all the information you've provided in the comments, here is the answer.

The query you're looking for is:

SELECT COUNT(DISTINCT student_id)
FROM tbl2
WHERE student_id NOT IN (SELECT student_id FROM tbl2 WHERE isPassed = 0)

It gets the number of students who passed all the subjects.

The nested query is:

SELECT student_id FROM tbl2 WHERE isPassed = 0

It gets the students who failed at least once.

An SQLFiddle to demonstrate it: http://sqlfiddle.com/#!9/a6baf4/1

Upvotes: 1

Jerodev
Jerodev

Reputation: 33196

You can use aggregate functions to get a count per group

Marks::where('section_id', '=', 1)
    ->where('isPassed', '=', 1)
    ->groupBy('class_room_id')
    ->selectRaw('COUNT(1) as studentCount')
    ->get();

This query will return a row per class_room_id with a value studentCount. If you want only one or a few class rooms, you can add where filters to the query.

Upvotes: 0

Related Questions