Jump to content

Student subject Positioning based on score using php and mysql database


sule

Recommended Posts

Please I am at a confused I need a code to generate the student position in subject 

SELECT marks.studentid, marks.studentname, marks.acayearid, marks.termid,marks.subjectid, marks.classid, marks.studentid, marks.staffname, marks.class_score2, marks.exam_score2, marks.total, marks.grade, marks.pos, marks.rem,
RANK() OVER (PARTITION BY marks.termid, marks.classid, subjectid ORDER BY marks.total DESC) pos
FROM marks;

pos1.jpg

pos2.jpg

code.jpg

Link to comment
Share on other sites

I noticed that you've mentioned pos twice in your SELECT clause. Here's a revised version of your SQL query:

 

SELECT
    marks.studentid,
    marks.studentname,
    marks.acayearid,
    marks.termid,
    marks.subjectid,
    marks.classid,
    marks.staffname,
    marks.class_score2,
    marks.exam_score2,
    marks.total,
    marks.grade,
    RANK() OVER (PARTITION BY marks.termid, marks.classid, marks.subjectid ORDER BY marks.total DESC) as pos,
    marks.rem
FROM
    marks;

 

Link to comment
Share on other sites

SELECT
    marks.studentid,
    marks.studentname,
    marks.acayearid,
    marks.termid,
    marks.subjectid,
    marks.classid,
    marks.staffname,
    marks.class_score2,
    marks.exam_score2,
    marks.total,
    marks.grade,
    RANK() OVER (PARTITION BY marks.termid, marks.classid, marks.subjectid ORDER BY marks.total DESC) as pos,
    marks.rem
FROM
    marks WHERE marks.subjectid='CREATIVE ARTS' OR marks.subjectid='ENGLISH' OR marks.subjectid='HISTORY' OR marks.subjectid='ICT' OR marks.subjectid='HISTORY' OR marks.subjectid='MATHEMATICS' OR marks.subjectid='SCIENCE' OR marks.subjectid='O. W. O. P.' ORDER BY marks.studentname ASC;

Edited by sule
Link to comment
Share on other sites

On 11/18/2023 at 2:38 PM, Barand said:

Hint: Instead of a string of OR conditions it is much simpler to use IN(...)

WHERE marks.subjectid IN ('CREATIVE ARTS', 'ENGLISH', 'HISTORY', 'ICT', 'MATHEMATICS', 'SCIENCE', 'O. W. O. P.') 

 

Still after the query it can only display one student. But i want display all the students in the class. I need your help.

Link to comment
Share on other sites

13 hours ago, Barand said:

I'd love to help but my query is having difficulty reading that picture of your data.

wait I'm sorry this is wildly off-topic but are you really 74 years old? (if so, that's insanely cool and I'd love to ask you some questions)

Edited by phpiodore
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.