1

I'm looking for a way to dynamically build up the number of columns in a query.

Imagine a school assessment database. I desire a way to get, for each question the percentage of students who got a certain mark. The problem is there is a dynamic number of marks available for each question.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE mark
(
  id       INT   NOT NULL AUTO_INCREMENT
    PRIMARY KEY,
  mark     FLOAT NOT NULL,
  question INT   NOT NULL,
  student  INT   NOT NULL
);


CREATE TABLE question
(
  id          INT   NOT NULL AUTO_INCREMENT
    PRIMARY KEY,
  maxMark     FLOAT NOT NULL,
  description TEXT  NOT NULL
);


INSERT INTO question (description, maxMark) VALUES ('Question 1', 2);
INSERT INTO question (description, maxMark) VALUES ('Question 2', 3);

INSERT INTO mark (mark, question, student) VALUES (1, 1, 1);
INSERT INTO mark (mark, question, student) VALUES (2, 1, 2);
INSERT INTO mark (mark, question, student) VALUES (3, 2, 1);
INSERT INTO mark (mark, question, student) VALUES (0, 2, 2);

Query 1: The closest I have been able to come up with is:

SELECT
  question.description,
  avg(mark),
  avg(mark) / question.maxMark  AS percent,
  question.maxMark,
  SUM(CASE WHEN mark = 0
    THEN 1
      ELSE 0 END) / count(mark) AS zeroPercent,
  SUM(CASE WHEN mark = question.maxMark
    THEN 1
      ELSE 0 END) / count(mark) AS fullPercent
FROM mark
  INNER JOIN question ON mark.question = question.id
GROUP BY question

Results: Which produces the below result:

| description | avg(mark) | percent | maxMark | zeroPercent | fullPercent |
|-------------|-----------|---------|---------|-------------|-------------|
|  Question 1 |       1.5 |    0.75 |       2 |           0 |         0.5 |
|  Question 2 |       1.5 |     0.5 |       3 |         0.5 |         0.5 |

Desired result:

+-------------+-----------+-----------+-----------+-----------+-----------+
| description | avg(mark) | percent 0 | percent 1 | percent 2 | percent 3 |
+-------------+-----------+-----------+-----------+-----------+-----------+
| Question 1  | 1.5       | 0         | 0.5       | 0.5       | NULL      |
+-------------+-----------+-----------+-----------+-----------+-----------+
| Question 2  | 1.5       | 0.5       | 0         | 0         | 0.5       |
+-------------+-----------+-----------+-----------+-----------+-----------+

Here is something similar, but it builds up the query based on distinct results, Mine needs to build it off question.maxMark.

  • You need to pivot? – Rick James Jan 25 '18 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.