Extracting best 8 done subjects from many done subjects using SQL and PHP

Question

Am developing a school report card system .

The student can done 14 subjects but on grading, the system should pick on the best 8 done subject and SUM the points of the 8 subject . The system use this SUM to grade the student. I tried to use ORDER BY point ASC and LIMIT 8 , this display the best 8 done subject but when it comes to SUM it SUM the points of all subjects done by the student.

What could be the problem?

SELECT stid, subid, total, point FROM oresults WHERE stid= '2' ORDER BY point ASC;

+-------+-------+-------+
| subid | total | point |
+-------+-------+-------+
|     3 |  78.5 |     1 |
|    10 |  71.5 |     2 |
|    12 |    65 |     4 |
|     9 |    61 |     4 |
|     4 |  62.5 |     4 |
|     5 |  56.5 |     5 |
|     8 |    55 |     6 |
|    14 |    52 |     6 |
|     6 |  50.5 |     6 |
|     1 |    45 |     8 |
|    11 |  31.5 |     9 |
|    13 |  39.5 |     9 |
|     7 |  37.5 |     9 |
|     2 |  28.5 |     9 |
+-------+-------+-------+

SELECT stid, subid, total, point FROM oresults WHERE stid= '2' ORDER BY point ASC LIMIT 8;

+-------+-------+-------+
| subid | total | point |
+-------+-------+-------+
|     3 |  78.5 |     1 |
|    10 |  71.5 |     2 |
|    12 |    65 |     4 |
|     9 |    61 |     4 |
|     4 |  62.5 |     4 |
|     5 |  56.5 |     5 |
|     8 |    55 |     6 |
|    14 |    52 |     6 |
+-------+-------+-------+

SELECT sum(point) FROM oresults WHERE stid= '2' ORDER BY point ASC LIMIT 8;

+------------+
| SUM(point) |
+------------+
|         82 |
+------------+

List item

Please take a look at [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and then edit your question accordingly. — Brien Foss, Feb 10 '18 at 05:04
Never screenshot your example tables. Never screenshot code. Always include it in your question as text. — Andrew, Feb 10 '18 at 14:37
Be specific about what defines the "best" 8. Based on the table I would think highest total, based on your sort it looks like lowest points. — Andrew, Feb 10 '18 at 18:14

Andrew · Accepted Answer · 2018-02-10T15:02:14.257

0

You seem to misunderstand how 'LIMIT' works, 'LIMIT' is applied at the end, you are not limiting the number of results in the group, you are limiting the number of students to return. Using that you can build you query like this however:

SELECT SUM(point) FROM 
  (SELECT point FROM oresults WHERE stid= '2' ORDER BY point ASC LIMIT 8) ss

SQLFiddle

There are more elegant solutions using windowing functions that don't require you to only look at one student at a time. These may or may not work, depending on your MySQL version, they will work on newer versions of MySQL, but not older versions, which don't support ROW_NUMBER() or OVER().

Select stid, subid, total, point, SUM(point) OVER(partition by stid) as pointSum
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY stid ORDER BY point DESC) rowNum FROM report_card) ss
WHERE rowNum<=8

This will give you the top 8 rows, with an extra column, pointSum which is the sum of the points in the top 8 rows by points, for each stid.

SQLFiddle

If you just one one row per stid, with just the ID and total you can do this instead

SELECT stid, SUM(point) as pointSum
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY stid ORDER BY point DESC) rowNum FROM report_card) ss
WHERE rowNum<=8
GROUP BY stid

SQLFiddle

edited Feb 10 '18 at 15:02

answered Feb 10 '18 at 05:16

Andrew

1,544
1
18
36

yes but i want to sum the points of the best 8 done subjects . – Ngode Danuel Feb 10 '18 at 05:27
Then what you want is this, you want to `group` by your stid - https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group – Andrew Feb 10 '18 at 05:34
yes but i want to sum the points of the best 8 done subjects . The challenge am having is, with display, it display 8 best done subject but when it comes to summing the total, it is summing all the points for all subjects yet i also included the limit of 8. What could be the problem? – Ngode Danuel Feb 10 '18 at 05:34
@NgodeDanuel Did you try either of the windowing options I gave? If they don't work your school is using an old mysql system, but now that I know you are using MySQL that first option I added will work. – Andrew Feb 10 '18 at 15:03

Extracting best 8 done subjects from many done subjects using SQL and PHP

1 Answers1