I want to count how many lessons of a subject a student has done

  • Resolved Mushfiqur

    (@aa11plus)


    6 months, 3 weeks ago

    I have a lesson report table which is updated by all teachers. It is accessed via a shortcode on our website. Amongst the columns that exist in the table is ‘student name’ and ‘subject’. I would like to create a view for admin staff to see how many lessons of each subject a student has done. The student name column is populated from a dropdown menu and the subject column is populated from a dropdown menu. So basically, I’d like to count the number of entries for each subject per student and display it in a new view. This will tell us how many times a student has had each subject and we can adjust their timetable so the lessons even out or we can group students and top up on a subject. What would be ideal is if we could create a new view with the student’s name in one column and then the subject names as headers with the count of how many times the subject appears for that student. Is this possible, please, and how can it be done?

    • This topic was modified 6 months, 3 weeks ago by Mushfiqur.
Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Contributor Kim L

    (@kimmyx)

    Hi @aa11plus,

    Thanks for the post!

    Is the current table a data table? If yes, you might find the Search Pane filter useful for your use case.

    See here: Search Panes | WP Data Access

    You just need to select the student name and subject columns in the configuration.

    Let us know if this works for you or if you need more help implementing it. ??

    Thread Starter Mushfiqur

    (@aa11plus)

    Yes, it’s a bog standard WP Data Access table…
    Thanks, I’ll have a read of the Search Panes | WP Data Access pages and see if I can find what I need. Thans for the links!

    Plugin Contributor Kim L

    (@kimmyx)

    Hi @aa11plus,

    You’re welcome! ??

    Feel free to send a follow up if you have other questions.

    Thread Starter Mushfiqur

    (@aa11plus)

    Here’s my follow-up (lol). Bear in mind I don’t know much SQL:

    SELECT aletheia_lesson_report.report_student as ‘Report_student’
    , aletheia_lesson_report.report_subject as ‘Report_subject’
    FROM aletheia_lesson_report
    ORDER BY aletheia_lesson_report.report_student asc

    I’ve just used the table view and I’ve added the above. So, aletheia_lesson_report is the table and student and subject are columns in the table. I’ve got a view that now lists student name to subject. And as expected, I can see rows of student names (multiple rows for each student) with lots of rows with the same subject. Examples of subjects are: Biology, Chemistry, Physics, Maths, English. What should I do to return student name to subject count, please? For example there are five subjects above. So, I should see the student’s name 5 times and in each row I should see the subject column and a count column of how many subject rows there are for that student. Basically, how many times have they had Biology, Chemistry, Physics, Maths, English.

    The data comes from teachers inputting into the lesson report table what student they taught and what subject they taught the student. Would be extremely grateful if you could point me in the right direction. From the example in your link, I couldn’t work out how to adjust the count(empno) for my scenario.

    Thread Starter Mushfiqur

    (@aa11plus)

    Apologies, an addition:
    SELECT aletheia_lesson_report.report_student as ‘Report_student’
    , aletheia_lesson_report.report_subject as ‘Report_subject’
    FROM aletheia_lesson_report
    WHERE (aletheia_lesson_report.report_subject = ‘Maths’)
    ORDER BY aletheia_lesson_report.report_student asc

    I’ve just worked out how to filter for a specific subject!

    • This reply was modified 6 months, 2 weeks ago by Mushfiqur.
    • This reply was modified 6 months, 2 weeks ago by Mushfiqur.
    Plugin Contributor Kim L

    (@kimmyx)

    Hi @aa11plus ,

    Thanks for the follow-up.

    Do you have a visual of the ideal resulting table? It looks like there isn’t a “count” column originally and you want to create it using SQL, am I correct?

    If you could send us more details about your expected results, we’ll do our best to help. ??

    We’ll wait for your reply!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘I want to count how many lessons of a subject a student has done’ is closed to new replies.