Aggregate Sum based on Condition in Kusto

Question

I have the below query and I would like to find the total number of exams a student has taken in a school year as a new column called totalExamsTaken

StudentExam table has fields of studentID, examDesc, examGrade, schoolYear StudentInfo table has fields of studentID, studentName, age, gender

StudentInfo
| lookup StudentExams on studentID
| project studentID, studentName, age, gender, examDesc, examGrade, schoolYear

In the end I would like to create a table that has columns of studentID, studentName, age, gender, schoolYear, totalExamsTaken.

StudentID StudentName Age Gender SchoolYear TotalExamsTaken
 
0001       John Doe    15  Male     2019        13
0001       John Doe    15  Male     2018        19
0001       John Doe    15  Male     2017        15
0002       Jane Doe    16  Female   2019        13
0002       Jane Doe    16  Female   2018        19
0002       Jane Doe    16  Female   2017        12

Is this possible?

Answer 1

you could try something like this, using the lookup operator:

let examCountByStudentId = 
   StudentExams
   | summarize totalExamsTaken = count() by studentId, schoolYear
;
StudentInfo
| lookup examCountByStudentId on studentId