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|
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(*)*100.00 / SUM(COUNT(*)) OVER() as [perc students %],
SUM(COUNT(*)) OVER() as [total students]
( 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
RIGHT JOIN [main_db].[dbo].[students] ON [scores].STUDENTNUMBER = [students].STUDENTNUMBER
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.
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?
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