All geek questions in one place

Find the difference between two fields as columns in a table

I have the following sql below

SELECT ttstudent.ttstudentid, ttstudent.studentid, ttstudent.subjectid, ttstudent.classnumber, ttstudent.classid, concat(student.fn, " ", student.sn) AS Student, SUM(If(ondemand.cycle="Feb7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 Feb`, SUM(If(ondemand.cycle="Jul7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 July`, SUM(If(ondemand.cycle="Feb8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 Feb`, SUM(If(ondemand.cycle="Jul8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 July`, SUM(If(ondemand.cycle="Feb9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 Feb`, SUM(If(ondemand.cycle="Jul9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 July`, SUM(If(ondemand.cycle="Feb10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Feb`, SUM(If(ondemand.cycle="Jul10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Aug`, ondemand.Student_ID FROM ttstudent INNER JOIN student ON ttstudent.studentid = student.code INNER JOIN ondemand ON ttstudent.studentid = ondemand.Student_ID GROUP BY ondemand.Student_ID

This generates about 25 people in the column with the last column, a computed field that finds the difference between the last 2 values ​​in the table. Ratings have timestamps.

 CODE |Year7Feb|Year7Jul|Year8Feb|Year8Jul|Year9Feb|Year9Jul| Year10Feb| Growth abe1 | 2.3 | 2.9 | | | | | | .6 bas1 | | | 3.5 | 3.7 | | | | .2 cod | | | | | | 4.5 | 5.2 | .7

What I would like to do is add another column that will take the last two accounts from each user (depending on which column he is in) and find the difference. I would call this growth a column.

I fight for what to use besides max. Any ideas?

+4
source share
1 answer

This should do the trick:

Compute growth column with the following query

 SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM ( SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN ( SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand GROUP BY Student_ID,type ) od ON si=Student_ID AND ty=type AND cylast>test_date GROUP BY si,ty, cylast ) getlast2 INNER JOIN ondemand la ON la.Student_Id=si AND la.type=ty AND la.test_date=cylast INNER JOIN ondemand pr ON pr.Student_Id=si AND pr.type=ty AND pr.test_date=cyprev

and then LEFT JOIN in your general request (a slightly simplified version here):

 SET @subj:="Numeracy"; SELECT Student_id, SUM(If(ondemand.cycle="Feb7" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr7 Feb`, SUM(If(ondemand.cycle="Jul7" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr7 July`, SUM(If(ondemand.cycle="Feb8" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr8 Feb`, SUM(If(ondemand.cycle="Jul8" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr8 July`, SUM(If(ondemand.cycle="Feb9" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr9 Feb`, SUM(If(ondemand.cycle="Jul9" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr9 July`, SUM(If(ondemand.cycle="Feb10" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr10 Feb`, SUM(If(ondemand.cycle="Jul10" and ondemand.type=@subj , ondemand.Score, NULL)) AS `Yr10 Aug`, growth FROM ondemand LEFT JOIN ( SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM ( SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN ( SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand GROUP BY Student_ID,type ) od ON si=Student_ID AND ty=type AND cylast>test_date GROUP BY si,ty, cylast ) getlast2 INNER JOIN ondemand la ON la.Student_Id=si AND la.type=ty AND la.test_date=cylast INNER JOIN ondemand pr ON pr.Student_Id=si AND pr.type=ty AND pr.test_date=cyprev ) gt ON si=Student_id AND ty=@subj GROUP BY Student_id;

I left JOIN for student and ttstudent and their columns

 ttstudent.ttstudentid, ttstudent.studentid, ttstudent.subjectid, ttstudent.classnumber, ttstudent.classid, concat(student.fn, " ", student.sn) AS Student

Edit:

Made changes only using the test_date column. The subquery has been tested in MySQL, I hope it also works in your database.

Edit2:

I slowly see where you are from. This is getting more complicated (just filling in the xtra conditions regarding type="Numeracy" ... Maybe there is an easier solution in the end?

Anyway, here is SQLfiddle to demonstrate all this (and the version is changed here: sqlfiddle2 ).

3rd and final editing:

What you probably want is something like SQLfiddle3 (-> only one SELECT statement without the previous SET statement). Your complete command should look something like this:

 SELECT ttstudent.ttstudentid, ttstudent.studentid, ttstudent.subjectid, ttstudent.classnumber, ttstudent.classid, concat(student.fn, " ", student.sn) AS Student, SUM(If(ondemand.cycle="Feb7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 Feb`, SUM(If(ondemand.cycle="Jul7" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr7 July`, SUM(If(ondemand.cycle="Feb8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 Feb`, SUM(If(ondemand.cycle="Jul8" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr8 July`, SUM(If(ondemand.cycle="Feb9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 Feb`, SUM(If(ondemand.cycle="Jul9" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr9 July`, SUM(If(ondemand.cycle="Feb10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Feb`, SUM(If(ondemand.cycle="Jul10" and ondemand.type="Numeracy", ondemand.Score, NULL)) AS `Yr10 Aug`, ondemand.Student_ID, getdif.growth FROM ttstudent INNER JOIN student ON ttstudent.studentid = student.code INNER JOIN ondemand ON ttstudent.studentid = ondemand.Student_ID LEFT JOIN ( SELECT si,ty,la.uid laid,pr.uid prid,(la.score-pr.score) growth FROM ( SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN ( SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand GROUP BY Student_ID,type ) od ON si=Student_ID AND ty=type AND cylast>test_date GROUP BY si,ty, cylast ) getlast2 INNER JOIN ondemand la ON la.Student_ID=si AND la.type=ty AND la.test_date=cylast INNER JOIN ondemand pr ON pr.Student_ID=si AND pr.type=ty AND pr.test_date=cyprev ) getdif ON si=ondemand.Student_ID AND ty=ondemand.type WHERE ondemand.type='Numeracy' GROUP BY ondemand.Student_ID

My previous versions reflect my personal preference for avoiding any redundancy, as well as my ambition to parameterize things as much as possible. But I'm probably too far away -).

+6
source

Source: https://habr.com/ru/post/1495089/

More articles:


All Articles