CODEWITHSUNDEEP
sqlsql-server
Mikkelrh
Mikkelrh

Reputation: 3

MS SQL: Joining three tables and calculating average only from numeric values

i need to join two tables based on PERSON_ID, and collecting an average of each person_ID's average grade, only including the numeric values. The two tabels are called students and Grades. I need to combine the columns PERSON_ID, ENROLL_PERIOD and GRADE.
So far i have tried 

select students.PERSON_ID, students.ENROLL_PERIOD, AVG(Cast(grades.GRADE as numeric))
from Students
INNER JOIN Grades on Students.PERSON_ID = Grades.PERSON_ID
where ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
Order by ENROLL_PERIOD ASC
GROUP BY PERSON_ID,ENROLL_PERIOD

I am getting an error:

Incorrect syntax near the keyword 'GROUP'. [156] (severity 15)

The table looks like this:

PERSON ID : ENROLL_PERIOD : GRADE
_________________________________
12401     :  109          :  4 
12401     :  109          :  7
12401     :  109          :  B
43245     :  112          :  12
43245     :  112          :  IB

I am not sure, if i am even heading in the right direction, so if anyone can help me, it would be very much appreciated. I am very new to MS SQL.

Upvotes: 0

Views: 59

Answers (3)

GuidoG
GuidoG

Reputation: 12039

You need to put the order by as last statement, and you need to rule out all non numerical values before you can use them in the avg function.
Something like this maybe

declare @students table(person_id int, enroll_period int)
declare @grades table(person_id int, grade varchar(2))

insert into @students values (12401, 109), (43245, 112)
insert into @grades values(12401, '4'), (12401, '7'), (12401, 'B'), (43245, '12'), (43245, 'IB')

select s.PERSON_ID, 
       s.ENROLL_PERIOD,
       avg( case when isnumeric(g.grade) = 1 then convert(int, g.grade) else null end) as AvgGrade
from   @students s
  INNER JOIN @grades g on s.PERSON_ID = g.PERSON_ID
where  ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
GROUP BY s.PERSON_ID, ENROLL_PERIOD
Order by ENROLL_PERIOD ASC

This returns the following result

PERSON_ID   ENROLL_PERIOD   AvgGrade    
---------   -------------   ------- 
12401       109             5   
43245       112             12

EDIT:
on your actual tables it probably will look like this :

select s.PERSON_ID, 
       s.ENROLL_PERIOD,
       avg( case when isnumeric(g.grade) = 1 then convert(int, g.grade) else null end) as AvgGrade
from   students s
  INNER JOIN grades g on s.PERSON_ID = g.PERSON_ID
where  ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''
GROUP BY s.PERSON_ID, ENROLL_PERIOD
Order by ENROLL_PERIOD ASC

Upvotes: 2

Rajat Jaiswal
Rajat Jaiswal

Reputation: 643

select students.PERSON_ID, students.ENROLL_PERIOD, AVG(Cast(grades.GRADE as numeric))
from Students
INNER JOIN Grades on Students.PERSON_ID = Grades.PERSON_ID
where ENROLL_PERIOD IS NOT NULL AND ENROLL_PERIOD <> ''

GROUP BY students.PERSON_ID,students.ENROLL_PERIOD
Order by students.ENROLL_PERIOD ASC

Order by should be after group by

Upvotes: 0

jr_jr
jr_jr

Reputation: 11

You need to place of group by syntax before order by.

Quoting from https://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/

The sql syntax order : 

    SELECT yourcolumns
    FROM tablenames
    JOIN tablenames
    WHERE condition
    GROUP BY yourcolumns
    HAVING aggregatecolumn condition
    ORDER BY yourcolumns

Upvotes: 0

Related Questions