Counting a value only if it appears more than once in a table.

Published 2 months ago by twoarmtom

I'm having trouble getting a count based on a special condition.

Basically, a "completed competency" is one that receives an evaluation of 3 at least two times. The following code works for counting how many distinct competencies have an evaluation of 3, but I want it to only count them if the distinct competency_id has an evaluation of 3 at least twice in the database for the specified student. Any suggestions?

$completed_competencies = DB::table('submitted_competencies')
                                ->where('student_id', 202)
                                ->where('evaluation', 3) // I want this to happen twice for the same competency_id before it is counted.
                                ->distinct('competency_id')
                                ->count('competency_id');
Best Answer (As Selected By twoarmtom)
twoarmtom

I found a way to do it by adding another column to the table that is set to true if the evaluation for the student and competency is given the value of 3 a second time.

In my controller, after evaluation is created:

if (SubmittedCompetency::where('competency_id', $evaluation->competency_id)
                    ->where('student_id', $evaluation->student_id)
                    ->where('evaluation', 3)
                    ->count() == 2
                ) 
            {
                $evaluation->update([
                    'isEtP' => true
                ]);
            }

Then I run the following query in the view (because there is more than one student on the page) to get the count foreach student:

$completed_competencies = DB::table('submitted_competencies')
                                ->where('student_id', $student->id)
                                ->where('isEtP', 1)
                                ->count();

Blade:

{{ $completed_competencies }} Complete

If someone can think of a better solution, I'd be happy to learn. At least now it is working properly.

Danchez

I haven't tested this myself, and your query looks right - shouldn't the last line be just count?

->count();

mhmd_daka

Hey,

You need to use Having with GroupBy,

​$completed_competencies = DB::table('submitted_competencies')
                        ->select('evaluation', DB::raw('Count(evaluation) as total_evaluation'))
                        ->groupBy('evaluation')
                        ->havingRaw('Count(evaluation) > 1')
                        ->get();​

Unfortunately I dont' have the time to test it, but I hope it will give some insight.

Thanks

twoarmtom

@Danchez The query works for counting how many evaluations has a value of 3 that belong to the student, but I only want the count if they have received the score of 3 more than once.

twoarmtom

@mhmd_daka Unfortunately, it does not work. And it ignore a few things. 1) I need it to be only competencies belong to a certain students. 2) I only want it to count the row belonging to the student and specific competency if the evaluation score was 3 more than once. In other words, the student received a perfect score (3) at least twice before it shows up on a report (which I'm displaying as a numeric count).

I tried adjusting the code to consider these things, and it counts all distinct competencies with evaluation of 3 belonging to a student, but it still counts it even it only happened once. I want it to ignore it unless it occurs twice in the table.

$completed_competencies = DB::table('submitted_competencies')
                                ->where('student_id', $student->id)
                                ->where('evaluation', 3)
                                ->groupBy('competency_id')
                                ->havingRaw('Count(competency_id) > 1')
                                ->count('competency_id');

With "competency_id", it seems to at least group the distinct competencies that occur more than once. If I use "evaluation" above for groupBy and havingRaw, it just does a total count where evaluations are 3, even if its the same competency multiple times.

Danchez

I see, I think I understand the problem now. I'm still learning Eloquent, but have some SQL experience - Did you mean something like this?

http://sqlfiddle.com/#!9/d2ae34/7

twoarmtom

@Danchez close, but I want to know when evaluation >2 for the distinct competency happens at least twice in the table. This example counts it even if it only appears once in the table.

twoarmtom

I found a way to do it by adding another column to the table that is set to true if the evaluation for the student and competency is given the value of 3 a second time.

In my controller, after evaluation is created:

if (SubmittedCompetency::where('competency_id', $evaluation->competency_id)
                    ->where('student_id', $evaluation->student_id)
                    ->where('evaluation', 3)
                    ->count() == 2
                ) 
            {
                $evaluation->update([
                    'isEtP' => true
                ]);
            }

Then I run the following query in the view (because there is more than one student on the page) to get the count foreach student:

$completed_competencies = DB::table('submitted_competencies')
                                ->where('student_id', $student->id)
                                ->where('isEtP', 1)
                                ->count();

Blade:

{{ $completed_competencies }} Complete

If someone can think of a better solution, I'd be happy to learn. At least now it is working properly.

Please sign in or create an account to participate in this conversation.