There is a task to form the basis for personal goals. Baseline data more than 2000 tasks 5 employees 2 types of assessments:

1) Quality assessment on a scale of super, ex, chorus, ud, neud, horror, disgrace.
2) In the second scale of assessments of 6 types of assessments, let it be 1,2,3,4,5,6.

Question: how to determine the final grade for the task of the assessment of the majority of workers. Question 2: How to determine the most difficult tasks?

For example, all 5 rated. Or 2 people have one assessment and the rest are different (two people’s assessment will be correct) and another variant of the final assessment: the average assessment is an example: if the majority does not exist, then the assessment is formed as follows;

Note: The second scale is almost never taken into account!

Example table:

INSERT INTO Test ( `zadanie`, `Id_Otcenki`, `login`, `otsenka1`, `otsenka2`) VALUES (1,1,denis,хор,2), (2,1,denis,ужас,1), (3,2,denis,хор,3), (4,1,denis,супер,1), (1,2,sergey,хор,2), (2,2,sergey,удов,1), (3,1,sergey,отл,3), (4,2,sergey,супер,1), (1,3,denis,удов,2), (2,3,denis,ужас,1), (3,3,denis,хор,3), (4,3,denis,супер,1), (1,4,tim,ужас,2), (2,4,tim,срам,1), (3,4,tim,отл,3), (4,4,tim,супер,1), (1,5,alex,супер,2), (2,5,alex,удов,1), (3,5,alex,неуд,3), (4,5,alex,супер,1),

At the output you need to get something like this:

Job Number: Final Grade

  • 1: Choir
  • 2: not
  • 3: Choir
  • 4: super
  • 2
    Is this task for ruSO participants to test their skills? Or do you have problems with something? - Alexey Shimansky
  • In the skill of the participants, I have no doubt. - redcoub
  • All values ​​I was able to split the tables. In addition, with another task they helped here. - redcoub
  • Well, I did one task myself, but with this I have a stupor. And there is no special knowledge in sql. In - redcoub
  • it seems I did not see the questions in the quotation .... I ought to take them out of there ..... if they are what you want to know ... otherwise it’s so incomprehensible - Alexey Shimansky

1 answer 1

It seems that it will fit:

 SELECT zadanie, FLOOR(AVG(CASE otsenka1 WHEN 'супер' THEN 6 WHEN 'отл' THEN 5 WHEN 'хор' THEN 4 WHEN 'удов' THEN 3 WHEN 'неуд' THEN 2 WHEN 'ужас' THEN 1 WHEN 'срам' THEN 0 END)) FROM Test GROUP BY zadanie`

Groups by task, translates the contents of otsenka1 into a digital equivalent, calculates the arithmetic mean and rounds down. The result is a job and a digital score.

  • And what if the assessment is just the same text? - redcoub
  • From your example it follows that otsenka2 numeric and always present. It is impossible to take the arithmetic average of the textual evaluation ... You need to make sure that the digital equivalent of the evaluation necessarily falls into the table. -
  • ummm the second estimate itself is the least interesting. Wrote in a note (now italicized and bold) - redcoub
  • Well, as I said, you need to make sure that the digital equivalent is always. If not, then you can use the CASE WHEN END construct for replacement. -
  • Is there any option to equate text evaluations to digital ones and derive as you suggested? - redcoub