Average for each student
I have 2 tables:
Student:
id name
2 ABC
13 DEF
22 GHI
Semester:
id student_id sem marks
1 2 1 {"math":3, "physic":4, "chemis":5}
2 2 2 {"math":2.5, "physic":4.5, "chemis":5}
3 2 3 {"math":3, "physic":3.5, "chemis":4}
5 13 1 {"math":3, "physic":4, "chemis":5}
6 13 2 {"math":3, "physic":4, "chemis":5}
So for example with student_id = 2:
middle marks = ((3 + 4 + 5) / 3) + (2.5 + 4.5 + 5) / 3 + (3 + 3.5 + 4) / 3) / 3 = 3.83
Is there a way to query the average tag group by student ID?
student_id average number_of_sems
2 3.83 3
13 xxx 2
I am trying to count by topic:
SELECT
t1.student_id,
t1.count,
(SELECT sum(xx.count)
FROM
(SELECT (marks:: JSON ->> 'math') :: DOUBLE PRECISION AS count
FROM "Semester"
WHERE student_id= t1.student_id) AS xx)
FROM
(
SELECT
student_id,
count(1)
FROM "Semester"
GROUP BY student_id
) AS t1;
But I still don't know how to proceed. This might be a bad decision.
+3
source to share
3 answers
This includes students without labels:
SELECT st.id, avg(m.value)
FROM student st
LEFT JOIN semester se
ON st.id = se.student_id
LEFT JOIN LATERAL (SELECT value::numeric
FROM jsonb_each_text(se.marks)
) m
ON TRUE
GROUP BY st.id;
┌────┬────────────────────┐
│ id │ avg │
├────┼────────────────────┤
│ 2 │ 3.8333333333333333 │
│ 13 │ 4.0000000000000000 │
│ 22 │ │
└────┴────────────────────┘
(3 rows)
+2
source to share
try this:
select student_id
, avg((marks->>m)::float) average
, count(distinct sem) number_of_sems
from semestr s
join student t on s.student_id = t.id
left outer join json_object_keys(marks) m on true
group by student_id;
student_id | average | number_of_sems
------------+------------------+----------------
2 | 3.83333333333333 | 3
13 | 4 | 2
(3 rows)
updated
as pozs pointed out - we should probably count semesters without exams as early as semesters ...
+2
source to share
Thanks everyone for the idea that using json_each_text () and avg () is funtion. Thus, if the goal receives an average grade in each subject of 1 student:
SELECT
st.id,
json_data.key AS subject,
SUM(json_data.value::DOUBLE PRECISION) AS sum_value,
avg(json_data.value::DOUBLE PRECISION) AS avg_value
FROM student AS st,
json_each_text(st.marks::JSON) AS json_data
GROUP BY si.id, subject;
0
source to share