Statistical Count from a lookup Table

[zacksoft]

I have a look up csv file added, which looks like this,
The header contains subject names and student name, and then subsequent rows contain performances for each pupil

MATH ENGLISH NAME SCIENCE
good bad Timmy best
good good John better
better bad Alek good

good bad Priya good
beter best Arun best

The above table means Timmy is 'good' at MATH, 'bad' at ENGLISH and 'best' at SCIENCE.
SImilarly John is 'good' at MATH and ENGLISH and 'bad' at SCIENCE.
etc...

I want to know how many kids are good, bad and best at each subject.
in stats table and if possible in a visualization.

e.g. 3 kids are good at MATH(Timmy, JOHN , Priya)
2 kids are best at SCIENCE (Timmy, Arun)

My query starts like,
| inputlookup marks.csv
| stats ........

[somesoni2 · Accepted solution]

Here you go

| inputlookup marks.csv
| table NAME *
| untable NAME subject grade
| chart count over subject by grade

[koshyk]

Is it something you looking for?

|inputlookup marks.csv
| stats values(NAME) as students, count by ENGLISH

if yes, you can expand to other subjects

[zacksoft]

What are the subjects where the BEST count is less than 5 ?
i.e. What are those subjects' names where only 5 or less students perform as BEST.

[zacksoft]

It is counting the header name too . How do I exclude that?
Also, I have a lot of subjects, more than 50, how can I see them all in one query ? Is it possible..