April 26, 2018 at 12:44 pm
Hi all,
I have a problem joining the code without the underline and the code with underline. Comes up with an error message regarding the Group By Clause, and I can't figure out what to do. I've basically got 3 databases( 'grades', 'grade_types' and 'students') and want to join these..
-------------------------------------------------------------------------
Select Top 5 grades.Person_ID, SUM(CASE WHEN grade_types.Passing_Grade=0 THEN 1 ELSE 0 END) As CountedFails,
replace(replace((students.Enroll_Period % 2), '1', 'Spring'), '0', 'Fall') As Season,
replace(students.Enroll_Period,students.Enroll_Period,1949+cast(students.Enroll_Period as int)/2) as Year
From grades
Inner Join grade_types ON grades.Grade=grade_types.Grade
Inner Join students ON students.Person_ID=grades.Person_ID
Group By grades.Person_ID
Order By CountedFails
------------------------------------------------------------------------
Thank you.
April 26, 2018 at 12:48 pm
phmik17 - Thursday, April 26, 2018 12:44 PMHi all,
I have a problem joining the code without the underline and the code with underline. Comes up with an error message regarding the Group By Clause, and I can't figure out what to do. I've basically got 3 databases( 'grades', 'grade_types' and 'students') and want to join these..
-------------------------------------------------------------------------
Select Top 5 grades.Person_ID, SUM(CASE WHEN grade_types.Passing_Grade=0 THEN 1 ELSE 0 END) As CountedFails,replace(replace((students.Enroll_Period % 2), '1', 'Spring'), '0', 'Fall') As Season,
replace(students.Enroll_Period,students.Enroll_Period,1949+cast(students.Enroll_Period as int)/2) as Year
From gradesInner Join grade_types ON grades.Grade=grade_types.Grade
Inner Join students ON students.Person_ID=grades.Person_IDGroup By grades.Person_ID
Order By CountedFails
------------------------------------------------------------------------Thank you.
Would help if you also posted the actual error message, not that you just got an error. Looking at the code, however, I think I know the error. Try the following:
SELECT TOP 5
[grades].[Person_ID]
, SUM( CASE
WHEN [grade_types].[Passing_Grade] = 0
THEN 1
ELSE 0
END
) AS [CountedFails]
, REPLACE(REPLACE(([students].[Enroll_Period] % 2), '1', 'Spring'), '0', 'Fall') AS [Season]
, REPLACE([students].[Enroll_Period], [students].[Enroll_Period], 1949 + CAST([students].[Enroll_Period] AS INT) / 2) AS [Year]
FROM
[grades]
INNER JOIN [grade_types]
ON [grades].[Grade] = [grade_types].[Grade]
INNER JOIN [students]
ON [students].[Person_ID] = [grades].[Person_ID]
GROUP BY
[grades].[Person_ID]
, REPLACE(REPLACE(([students].[Enroll_Period] % 2), '1', 'Spring'), '0', 'Fall')
, REPLACE([students].[Enroll_Period], [students].[Enroll_Period], 1949 + CAST([students].[Enroll_Period] AS INT) / 2)
ORDER BY
[CountedFails];
April 26, 2018 at 12:55 pm
It's works perfectly. Thank you so much! 😉
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply