5

How to calculate the average score for each student

 3 years ago
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.
neoserver,ios ssh client

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 like note ~ '^[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, because NULL values do not count. Ypu could also use sum for that.
  • As class_id is a number type, bigint to be precise, leading zeros are just noise.
    Use class_id = 1 instead of class_id = 0001.

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK