Problem in Group By clause

  • 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.

  • phmik17 - Thursday, April 26, 2018 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.

    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];

  • 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