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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy