SELECT CONCAT_WS(" ",UM.first_name,UM.last_name) AS StudentName,lb.board_name,LS.school_name,LS.city AS SchoolCity,UM.gender,UM.user_id,LM.username,SCN.number,
LD1.department_name AS CourseJoined,
TUM.uni_id AS University_id,
TUM.uni_name AS CollegeJoined,
SOC.uni_name AS user_uni,
SOC.`status`,
tm.`country_name` AS CountryofCollegeJoined,
tcm.`city_name` AS CityofCollegeJoined,
TUM.`is_prestigious` AS CollegeJoinedIsPrestigious,
(SELECT MIN(UDR.rank) FROM `university_dept_ranking` UDR 
WHERE UDR.uni_reF_id=SOC.uni_ref_id AND UDR.department_ref_id=SOC.`dept_ref_id`
AND SOC.`status`='Joining' -- AND UDR.rank_year>2016
GROUP BY UDR.uni_reF_id,UDR.department_ref_id
) AS CollegeJoinedRank,
(SELECT COUNT(*) FROM student_outplacement_colleges AS SOC1  
LEFT JOIN tbl_university_master TUM1 ON TUM1.uni_id=SOC1.uni_ref_id WHERE SOC1.user_reF_id=UM.user_id AND TUM1.`is_user_entered`='0'
) AS NoofCollegesinAlumnisys,
SOC.`scholarship_received`,
LC.`currency_name`,
SOC.`amount`,
OP.`can_contact` AS 'can_juniors_connect_with_you?',
OP.modified_on,
(SELECT SE.overall_marks
FROM `student_education` SE
WHERE SE.class_level='10' AND SE.user_reF_id=UM.user_id) AS 10th_Overall_Marks,
(SELECT GROUP_CONCAT("",LBC.`board_marks` ,"-",SGC.`grade_count`)
FROM  `student_education` SE 
LEFT JOIN `student_grade_count` SGC ON SGC.`student_education_ref_id`=SE.`student_education_id`
LEFT JOIN `lookup_board_conversion` LBC ON LBC.`board_conversion_id`=SGC.`board_conversion_ref_id`
WHERE SE.class_level='10' AND SE.user_reF_id=UM.user_id) AS 10th_Grades,
(SELECT GROUP_CONCAT("",SGC.`grade_count`)
FROM  `student_education` SE 
LEFT JOIN `student_grade_count` SGC ON SGC.`student_education_ref_id`=SE.`student_education_id`
WHERE SE.class_level='10' AND SE.user_reF_id=UM.user_id) AS 10th_Grade_Count,
(SELECT SE.overall_marks
FROM `student_education` SE
WHERE SE.class_level='12' AND SE.user_reF_id=UM.user_id) AS 12th_Overall_Marks,
(SELECT GROUP_CONCAT("",LBC.`board_marks` ,"-",SGC.`grade_count`)
FROM  `student_education` SE 
LEFT JOIN `student_grade_count` SGC ON SGC.`student_education_ref_id`=SE.`student_education_id`
LEFT JOIN `lookup_board_conversion` LBC ON LBC.`board_conversion_id`=SGC.`board_conversion_ref_id`
WHERE SE.class_level='12' AND SE.user_reF_id=UM.user_id) AS 12th_Grades,
(SELECT GROUP_CONCAT("",SGC.`grade_count`)
FROM  `student_education` SE 
LEFT JOIN `student_grade_count` SGC ON SGC.`student_education_ref_id`=SE.`student_education_id`
WHERE SE.class_level='12' AND SE.user_reF_id=UM.user_id) AS 12th_Grade_Count,
(SELECT GROUP_CONCAT(ee.entrance_short_name)
FROM student_outplacement_tests sot JOIN entrance_exam ee ON ee.entrance_id = sot.entrance_ref_id 
WHERE sot.user_ref_id = UM.user_id ORDER BY ee.entrance_id) AS test_name,

(SELECT GROUP_CONCAT(IF(SOT.score IS NULL, 0, SOT.score))
FROM `student_outplacement_tests` SOT
WHERE SOT.user_reF_id=UM.user_id ORDER BY SOT.entrance_ref_id) AS Test_Score,

UM.passout_year
FROM tbl_si_students SS
JOIN tbl_user_master UM ON UM.user_id= SS.user_ref_id
JOIN login_master LM ON LM.login_id=UM.login_reF_id
LEFT JOIN lookup_school LS ON LS.school_id=UM.school_reF_id
JOIN `student_outplacement_colleges` SOC ON SOC.user_reF_id=UM.user_id 
LEFT JOIN tbl_university_master TUM ON TUM.uni_id=SOC.uni_ref_id
LEFT JOIN `outplacement_preferences` OP ON OP.user_reF_id=UM.user_id
LEFT JOIN lookup_department LD1 ON LD1.department_id = SOC.dept_ref_id
LEFT JOIN lookup_currency LC ON LC.`currency_id`=SOC.currency_reF_id
LEFT JOIN tbl_country_master tm ON tm.country_id = TUM.country_ref_id
LEFT JOIN tbl_city_master tcm ON tcm.city_id = TUM.city_ref_id
LEFT JOIN student_contact_number SCN ON SCN.user_ref_id=UM.user_id AND SCN.is_primary='1'
LEFT JOIN lookup_board lb ON board_id = UM.present_board_ref_id
WHERE  UM.is_test='N';