topic Re: Kindly build this in single query. in Getting Data In

Kindly build this in single query.

_Raj — Wed, 15 Oct 2025 16:06:42 GMT

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.

Re: Kindly build this in single query.

richgalloway — Wed, 15 Oct 2025 16:38:04 GMT

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.

Re: Kindly build this in single query.

livehybrid — Wed, 15 Oct 2025 21:31:44 GMT

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:

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

Re: Kindly build this in single query.

gcusello — Thu, 16 Oct 2025 06:28:36 GMT

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

Re: Kindly build this in single query.

PrewinThomas — Thu, 16 Oct 2025 06:48:14 GMT

@_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!

Re: Kindly build this in single query.

_Raj — Thu, 16 Oct 2025 10:32:32 GMT

@richgalloway

Could you pls validate my search 🙂

Thanks

Re: Kindly build this in single query.

richgalloway — Thu, 16 Oct 2025 14:46:46 GMT

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.

Re: Kindly build this in single query.

_Raj — Fri, 17 Oct 2025 11:02:11 GMT

Hi,

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

defination :

thanks..

Re: Kindly build this in single query.

richgalloway — Fri, 17 Oct 2025 12:06:50 GMT

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

Re: Kindly build this in single query.

vjdev — Sun, 19 Oct 2025 21:03:49 GMT

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!