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
... View more