How to Find MySQL Deviation Values

I would like to obtain the deviation value (not the standard deviation) for each subject for the test table with the subject and point columns. What should I do?

The standard deviation and average value were taken as follows.

SELECT subject, STD(point), AVG(point) from test_table GROUP BY subject;

I would like to calculate the standard deviation for each subject (average point for each point-subject) s standard deviation for each subject × 10+50 and store it in the output or column.

mysql

2022-09-30 18:44

1 Answers

I will correct the answer because I wrote it roughly on my cell phone.
I don't have a MySQL environment at hand, so I will answer with Transact-SQL.
I think the CASE expression is slightly different from the difference in standard deviation (STDEVP) function.

/*Data creation*/
DROP TABLE TEST_TABLE;

CREATE TABLE TEST_TABLE(
    ID VARCHAR(8),
    NAME VARCHAR (32) ,
    SUBJECT VARCHAR(16),
    POINT INT,
    CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID, NAME, SUBJECT)
);

INSERT INTO TEST_TABLE (ID, NAME, SUBJECT, POINT) VALUES ('10001', 'John', 'Japanese', 50);
INSERT INTO TEST_TABLE (ID, NAME, SUBJECT, POINT) VALUES ('10001', 'John', 'English', 80);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10001', 'John', 'Math', 40);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10001', 'John', 'Science', 60);
INSERT INTO TEST_TABLE (ID, NAME, SUBJECT, POINT) VALUES ('10001', 'John', 'History', 50);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10002', 'Lisa', 'Japanese', 60);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10002', 'Lisa', 'English', 20);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10002', 'Lisa', 'Math', 20);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10002', 'Lisa', 'Science', 20);
INSERT INTO TEST_TABLE (ID, NAME, SUBJECT, POINT) VALUES ('10002', 'Lisa', 'History', 100);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10003', 'Peter', 'Japanese', 10);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10003', 'Peter', 'English', 10);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10003', 'Peter', 'Math', 10);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10003', 'Peter', 'Science', 10);
INSERT INTO TEST_TABLE(ID, NAME, SUBJECT, POINT) VALUES('10003', 'Peter', 'History', 10);
/* SELECT*/
SELECT
     T1.ID ID
    , T1.NAME NAME
    , T1. SUBJECT SUBJECT
    ,T1.POINT POINT
    , CASE WHENT2.STDRD = 0THEN 50 / * 0 NO DIVISION* /
     ELSE ROUND(T1.POINT-T2.AVRG)/T2.STDRD*10+50,0)/*Integer*/
     END STDRD
from
    TEST_TABLE T1
INNER JOIN
    (
        SELECT
              SUBJECT
             , STDEVP (POINT) STDRD
             , AVG (POINT) AVRG
        from
            TEST_TABLE
        GROUP BY
            SUBJECT
    )T2
ON
    T1.SUBJECT=T2.SUBJECT
ORDER BY
    ID, NAME, SUBJECT
;

2022-09-30 18:44

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656