SQL Loop Over Name-CodePudding

I have:

TABLE1
    STUDENT CLASS   SCORE
    PAT A   14
    PAT A   10
    PAT B   13
    PAT B   20
    PAT C   11
    MARK    A   18
    MARK    A   13
    MARK    A   17
    MARK    A   10
    DAVIS   C   12
    JER A   15
    JER B   12
    JER C   20
    JER C   17
    BARB    C   14
    BARB    C   19
    BARB    C   11
    TOM A   18
    TOM A   12
    TOM B   16
    TOM B   12

and wish for to make:

STUDENT CLASS   AVERAGESCORE
PAT A   12
PAT B   16.5
PAT C   11
DAVIS   A   14.5
DAVIS   B   NA
DAVIS   C   NA
MARK    A   NA
MARK    B   NA
MARK    C   12
JER A   15
JER B   12
JER C   18.5

Which does this: for the STUDENT in the list, calculate AVERAGE(SCORE) BY CLASS AND STUDENT

I try this with no success:

LIST1 = PAT, MARK, DAVIS, JER
select STUDENT, CLASS, AVG(SCORE) OVER(PARTITION BY STUDENT, CLASS) AS AVERAGE_SCORE
from TABLE1
where STUDENT in LIST1

CodePudding user response：

select student, class, avg(score)
from table1
where student in ('PAT', 'DAVIS', 'MARK', 'JER')
group by student, class

CodePudding user response：

Try the following, you first need a list of all class / student combinations, to which you can outer-join and then aggregate:

with c as (select distinct class from t),
s as (select Student from(values('PAT'),('MARK'),('DAVIS'),('JER'))s(Student)),
a as (select Student, Class from s cross join c)

select a.Student, a.Class, Avg(t.Score * 1.0) AverageScore
from a 
left join t on t.class = a.class and t.Student = a.Student
group by a.Student, a.Class
order by a.Student, a.Class;