Reputation: 15
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
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
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