-3

Good morning gurus, I posted the below yesterday and I was told that similar question has been raised and answered. Please, can anyone give me the link to the similar answer given? Thanks

I'd.  Regno.  Class.    Subject      Score   
1.     111.      JSS1.    Mth.            60
2.     112.       JSS1.    Mth.           50
3.      111        JSS1.    Eng.          60
4.      112.       JSS1.    Eng.          80 etc

Please, how can I use Php and MySQL to use the above records to get the below records?

Regno.   Eng.      Math.    Total.    Position
111.        60.         60.         120.       2nd
112.        80.         50.          130.      1st
Devsi Odedra
  • 5,244
  • 1
  • 23
  • 37
jst
  • 11
  • 2
  • This is a standard pivot, and you've already been told precisely where to look. That said, to my mind, these issues are best resolved in application code – Strawberry Aug 09 '19 at 07:31
  • Just follow the link at the top of your previous question, no need to post the same question again – Ocaso Protal Aug 09 '19 at 07:32

1 Answers1

0

It's clunky, but it does the job. If you have many subjects, then it will get out of control, and you'll have to do something more advanced.

select t.*
, rank() over (order by total) as ranking
from    (
select regno
, sum(case when subject = 'Mth.' then score end) as math
, sum(case when subject = 'Eng.' then score end) as eng
, sum(score) as total
from test.scores
group by regno
) t

For reference: test.scores is your input table

Conor Flatley
  • 1
  • 1