Help getting count by letter grade based on score range

[Diana_a]

Hi,

I was hoping someone can help me, though I have seen some examples, but all of them involve case. 
I need to create a statistical table that would give me the letter grade, count, percentage and grand total as the last column (the last one is just for me to see the actual total)

Letter grade	Count	Perc %	Grand Total
A	33	33.33	100
B	33	33.33	100
C...	33...	33.33...	100 ...
no score	1	1	100

I wrote in SQL where I join 2 tables the ones with the SCORES and one with the STUDENTS. If a student does not have a score or it's a zero, they should just go to the no score bucket.

SELECT t.SCORE as [grade],
count(*) [counter],
count(*)*100.00 / SUM(COUNT(*)) OVER() as [perc students %],
SUM(COUNT(*)) OVER() as [total students]
FROM
( select case
when SCORE = 0 then 'no score or zero'
when SCORE between 1 and 59 THEN 'F'
when SCORE between 60 AND 69 THEN 'D'
when SCORE between 70 AND 79 THEN 'C'
when SCORE between 80 AND 89 THEN 'B'
else 'A' end as SCORE
FROM
[main_db].[dbo].[scores]
RIGHT JOIN [main_db].[dbo].[students] ON [scores].STUDENTNUMBER = [students].STUDENTNUMBER
) t
Group by t.SCORE

My SPL I have so far is that I need join them but I am having a hard time:

This contains the list of active student numbers:

index=main host=main source=students  sourcetype=db_students STUDENTNUMBER

And this contains all (inactive and active) student numbers and their scores.

index=main host=main source=scores sourcetype=db_scores
| dedup STUDENTID
| eval scoreRange=case(SCORE >= 90, "A",
SCORE >= 80 AND CREDIT_SCORE <= 89, "B",
SCORE >= 70 AND CREDIT_SCORE <= 79, "C",
SCORE >= 60 AND CREDIT_SCORE <= 69, "D",
SCORE >= 0 AND CREDIT_SCORE <= 59, "F"
)
| stats count(STUDENTID) by scoreRange

Don't know how to join the above to get the counts from active students (STUDENT table) only and don't know how to tell that if there's no score in the SCORES table, to give me "No Score".

I'd really appreciate any hints you can give me, I believe it may need tweaks because it needs to be more efficient and perhaps I don't need to use CASE.

Much appreciated!

Diana

[ITWhisperer]

Can you share some sample events from your student and scores sources?

Is SCORE and CREDIT_SCORE the same thing?

Which field tells you if the student is active?

Your calculations are different between the SQL and SPL for grade F and consequently "no score" - was this deliberate?

[gcusello · Accepted solution]

Hi @Diana_a,

about the first question,  you should try to use the evenstats command before the stats, remembering to add the result of evenstats to the stats, something like this:

index=main host=main source=scores sourcetype=db_scores
| eval scoreRange=case(SCORE >= 90,"A", SCORE >= 80 AND CREDIT_SCORE <= 89,"B", SCORE >= 70 AND CREDIT_SCORE <= 79,"C", SCORE >= 60 AND CREDIT_SCORE <= 69,"D", SCORE >= 0 AND CREDIT_SCORE <= 59, "F")
| evenstats dc(STUDENT_ID) AS STUDENT_COUNT
| stats count(STUDENTID) AS students values(STUDENT_COUNT) AS STUDENT_COUNT BY scoreRange

Ciao.

Giuseppe

[Diana_a]

I see, thank you for the tip! I am using it to check

[gcusello]

Hi @Diana_a ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @Diana_a,

good for you, see next time!

please accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors  😉