0

I have an SQL table in the following approximate format

+----+------------+------------+----------+------+
| id |  fin_date  | student_id | course_id| mark |
+----+------------+------------+----------+------+
|  1 | 2018-05-03 |      10049 |        1 |  60  |
|  2 | 2018-05-03 |      10032 |        2 |  45  |
|  3 | 2018-05-03 |      10032 |        1 |  88  |
|  4 | 2018-05-03 |      10032 |        1 |  96  |
+----+------------+------------+----------+------+

Unfortunately, I have just been asked to then have an index for a "student_course_id" sub-index that represents the number of courses that student had taken up until that time... Example below

+----+------------+------------+----------+------+-------------------+
| id |  fin_date  | student_id | course_id| mark | student_course_id |
+----+------------+------------+----------+------+-------------------+
|  1 | 2018-05-03 |      10049 |        1 |  60  |   1               |
|  2 | 2018-05-03 |      10032 |        2 |  45  |   1               |
|  3 | 2018-05-03 |      10032 |        3 |  88  |   2               |
|  4 | 2018-05-03 |      10032 |        4 |  96  |   3               |
|  5 | 2018-05-03 |      10049 |        2 |  60  |   2               |
+----+------------+------------+----------+------+-------------------+

This way when you lookup index id=3 between two dates, you then can immediately tell that its the 2nd course that student has taken.

I don't really want to add a new column to my table, is there any way to add this extra column in a similar way to COUNT(*) can be used to total unique fields in a single column?

theshadow124
  • 661
  • 3
  • 8
  • 29
  • If you're up to using an HTML table, you could use (an) alias(es) and then format them into it, set inside a loop. I know there's a way to do it in sql but I can't remember how it's done. The darn thing's on the tip of my tongue too. Edit: Here we go, I just remembered (10 secs. later). It's called a PIVOT (table) https://stackoverflow.com/questions/7674786/mysql-pivot-table. I think that can be used for this here. – Funk Forty Niner May 04 '18 at 00:58
  • Sorry, I should have mentioned above, this is an SQL table, everything being processed server side, but I am not grabbing the entire table when querying for a student, Hmm... I'll update the question with a little more information. – theshadow124 May 04 '18 at 01:01
  • ok I think I get the gist here. You can count specific columns while using aliases if needed, and running through this a SELECT with a GROUP BY and/or DISTINCT if you're looking for unique fields. The alias for the new column you want, would be used for it. – Funk Forty Niner May 04 '18 at 01:10
  • I actually managed to get it working using a subquery, thank you for taking time to try to help me :) – theshadow124 May 04 '18 at 01:12
  • Sure thing, *cheers*. – Funk Forty Niner May 04 '18 at 01:12

1 Answers1

0

Ok, I guess I asked a little prematurely. I figured it out...

SELECT *, ( select count(*) from `marks` t2 
where t1.student_id = t2.student_id and t1.id >= t2.id ) 
as student_course_id FROM `marks` as t1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
theshadow124
  • 661
  • 3
  • 8
  • 29
  • Glad to hear it, but did you already have some form of query that you tried but didn't work the way you wanted it to? – Funk Forty Niner May 04 '18 at 01:11
  • The query I need to run is very similar to above, but I would also be limiting by date range or something in the outer query, but this should work – theshadow124 May 04 '18 at 01:13
  • ok. Yet... about your *"but this should work"* - It should or it does? Two different animals here ;-) – Funk Forty Niner May 04 '18 at 01:13
  • I will know soon, I am putting it all into the report queries now(They generate a series of pdfs server side) the cron job will run in about 40 min here. It works for the queries I have tested with through phpmyadmin – theshadow124 May 04 '18 at 01:20
  • Your correlated subquery solution looks fine to me. You can also use an uncorrelated subquery or @variables to build a rolling total. These methods are worth investigating if performance becomes an issue, but I see no reason not to 'accept' your own answer – Strawberry May 04 '18 at 06:31