1
How to calculate the average score for each student
source link: https://www.codesd.com/item/how-to-calculate-the-average-score-for-each-student.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How to calculate the average score for each student
advertisements
I have a table "Register"
with columns:
class_id bigint NOT NULL,
disciple text,
datelesson date NOT NULL,
student_id integer NOT NULL,
note character varying(2)
now I want to calculate the average score for each student_id and the number of absent
Select * from "Register" as m
Join
(SELECT AVG(average), COUNT(abs) FROM (SELECT
CASE
WHEN "note" ~ '[0-9]' THEN CAST("note" AS decimal)
END AS average,
CASE
WHEN "note" ='a' THEN "note"
END AS abs
FROM "Register" ) AS average)n
on class_id=0001
And datelesson between '01.01.2012' And '06.06.2012'
And discipline='music' order by student_id
Result is this:
0001;"music";"2012-05-02";101;"6";7.6666666666666667;1
0001;"music";"2012-05-03";101;"a";7.6666666666666667;1
0001;"music";"2012-05-01";101;"10";7.6666666666666667;1
0001;"music";"2012-05-02";102;"7";7.6666666666666667;1
0001;"music";"2012-05-03";102;"";7.6666666666666667;1
0001;"music";"2012-05-01";102;"";7.6666666666666667;1
The result I receive is for the whole column but how do I calculate average marks for each student?
Could look like this:
SELECT student_id
, AVG(CASE WHEN note ~ '^[0-9]*$' THEN note::numeric
ELSE NULL END) AS average
, COUNT(CASE WHEN note = 'a' THEN 1 ELSE NULL END) AS absent
FROM "Register"
WHERE class_id = 1
AND datelesson BETWEEN '2012-01-01' AND '2012-06-06'
AND discipline = 'music'
GROUP BY student_id
ORDER BY student_id;
I added a couple of improvements.
- You don't need to double-quote lower case identifiers.
- If you want to make sure, there are only digits in
note
, your regular expression must be something likenote ~ '^[0-9]*$'
. What you have only checks if there is any digit in the string. - It's best to use the ISO format for dates, which works the same with any locale:
YYYY-MM-DD
. - The
count
for absence works, becauseNULL
values do not count. Ypu could also usesum
for that. - As class_id is a number type,
bigint
to be precise, leading zeros are just noise.
Useclass_id = 1
instead ofclass_id = 0001
.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK