CODEWITHSUNDEEP
sqlsql-server
Heer
Heer

Reputation: 15

How to get average for each student in sql?

I have 2 tables - Table1 and Table2:

Student ID   Student Name
-------------------------
12           John Smith
14           Raj Sharma
15           Lee Wang
16           Anan Obi

Student ID   Course           Points
-------------------------------------
12           Mathematics      86
14           Mathematics      75
16           Mathematics      96
16           Mathematics      97
15           Physics          92
15           Physics          65
12           Physics          63
16           Physics          58
14           Physics          78
14           Chemistry        83
15           Chemistry        65
12           Chemistry        95
12           Chemistry        90

I want to get average points per course for each student, sorted by student names.

Resulting output should be:

| name       | course | avg (points) |
+------------+--------+--------------+
| Anan Obi   | Math   | 96.50000     |
| Anan Obi   | Phys   | 58.00000     |
| John Smith | Math   | 86.00000     |
| John Smith | Phys   | 63.00000     |
| John Smith | Chem   | 92.50000     |
| Lee Wang   | Phys   | 78.50000     |
| Lee Wang   | Chem   | 65.00000     |
| Raj Sharma | Math   | 75.00000     |
| Raj Sharma | Phys   | 78.00000     |
| Raj Sharma | Chem   | 83.00000     |

I tried the following code but not getting desired output:

select 
    t1.'student name', 
    t2.'course', avg(points) 
from 
    table1 t1, table2 t2 
group by 
    t2.Course 
order by 
    t1.'student name'

Any help is appreciated.

Thanks

Upvotes: 0

Views: 107

Answers (2)

vahid
vahid

Reputation: 1

Very simple by using Window Functions

SELECT
    t1.Student Name,
    AVG(t2.Points) OVER (PARTITION BY t1.Student Name,t2.course)
FROM
    t1
    LEFT JOIN t2 ON t1.Student ID=t2.Student ID

Upvotes: 0

user3401335
user3401335

Reputation: 2405

Group by also over course

select s.[Student ID], s.[Student Name], sc.course, avg(sc.Points) 
from Students s
JOIN studentsCourse sc ON s.[Student ID]= sc.[Student ID]
Group by s.[Student ID], s.[Student Name], sc.course
order by s.[Student Name]

Upvotes: 4

Related Questions