Kindly build this in single query.

[_Raj]

Hi,

Sample Data (CSV format) – 
student_id,student_name,class,school,subject,score
1,Alice,10A,School1,Math,85
2,Bob,10A,School1,Math,72
3,Charlie,10B,School1,Science,90
4,David,10A,School2,Math,65
5,Eva,10B,School2,Science,88

How to achieve this Query : You need to get 2 ranks for students 1. At class level 2. At school level. Kindly build this in single query.

[vjdev]

Hello,

|makeresults format=csv data="student_id,student_name,class,school,subject,score
1,Alice,10A,School1,Math,85
2,Bob,10A,School1,Math,72
3,Charlie,10B,School1,Science,90
4,David,10A,School2,Math,65
5,Eva,10B,School2,Science,88"
| top score class school
| streamstats count as ClassLevel by class
| streamstats count as SchoolLevel by school

Thank You!

[_Raj]

Hi,

Could any one please help me in correcting the macros to get the ranking logic.

defination :

| eval $score_field$ = tonumber($score_field$)
| stats avg($score_field$) AS avg_score by $class_field$
| sort - avg_score
| streamstats count AS rank
| eval avg_score = round(avg_score,2)

thanks..

[richgalloway]

Change the name field to "class_ranking(2)".  The number of Arguments must be included in the macro name (unless there are no arguments).

[PrewinThomas]

@_Raj 

As others mentioned, its not completely clear. But to start with if you are looking both class-level and school-level ranks, i would suggest use streamstats to put both results into one table

eg:

| streamstats count as class_rank by class

| streamstats count as school_rank by school

Regards,
Prewin
If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[gcusello]

Hi @_Raj ,

as also @richgalloway and @livehybrid said, your requirements aren't so clear, do you want to have the count of students for each class and for each school or what else?

and how do you want to display these results in the same table?

probably you could create different panels different searches in a dashboard.

Anyway, if you want to have the results in a single table (I don't understand why!), you could run something like this (resusing the data visualization of @livehybrid 😞

| makeresults format=csv data=
     "student_id,student_name,class,school,subject,score
     1,Alice,10A,School1,Math,85
     2,Bob,10A,School1,Math,72
     3,Charlie,10B,School1,Science,90
     4,David,10A,School2,Math,65
     5,Eva,10B,School2,Science,88"
| stats 
     count(eval(class="10A")) AS 10A_count
     count(eval(class="10B")) AS 10B_count
     count(eval(school="School1")) AS School1_count
     count(eval(school="School2")) AS School2_count

Ciao.

Giuseppe

[livehybrid]

Hi @_Raj 

Based on your previous questions it sounds like you are working through some exam/course questions? Has there been guidance on how to work out this SPL? 

Im therefore hesitant to just give an answer, but perhaps the following can help. 

I would start by visualising the sample data using the makeresults command:

|makeresults format=csv data="student_id,student_name,class,school,subject,score
1,Alice,10A,School1,Math,85
2,Bob,10A,School1,Math,72
3,Charlie,10B,School1,Science,90
4,David,10A,School2,Math,65
5,Eva,10B,School2,Science,88"

Then I would probably use stats to get the scores for each class, within each school...

Then if you want to retain the class scores but also get the score for each school then you might want to look at using eventstats which will add a column to the existing stats. 

Sorry for not providing the full SPL but hopefully this will help 🙂 

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[richgalloway]

What have you tried so far and how did those attempts not meet expectations?  It would help to see a mock-up of the expected output.

[_Raj]

@richgalloway 

Could you pls validate my search 🙂

Thanks 

[richgalloway]

I can confirm the search produces the results shown.  I cannot verify these are the right results because you have said what the desired results are.  Nor can I test the search myself because the query is in a screenshot rather than in a code box.