Search code examples
azure-data-explorerkql

Count the Distinct Rows in Kusto

I have the below query and I would like to find the distinct rows based on studentID, examDesc, examGrade, schoolYear and add an extra column with the count on the distinct rows.

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

I was wondering what's the syntax for this?

Solution

  • you could use the count() aggregation function.

    for example:

    StudentExams 
| lookup StudentInfo on studentID // assuming not all information is in the 'StudentExams' table. otherwise, remove this line
| summarize count() by studentID, examDesc, examGrade, schoolYear

    [note: if this doesn't meet your requirement - it's strongly recommended that you provide a sample input data set, and the matching output for it, in order to clarify your desired outcome]