Database Reference
In-Depth Information
This returns the third choice. We usedthe
COLUMN_GET()
function to get the dynamic
column within the column given as the first argument. The second argument specifies the
key to use to get the data. We also included
AS
to indicate the type of data type it should
use (i.e.,
CHAR
) to cast the value it returns.
Now let's enter a bunch of answers for our members. If you're using an electronic version
of this topic, just copy and paste the following into your MariaDB server:
INSERT INTO
survey_answers
(
human_id
,
question_id
,
date_answered
,
answer
)
VALUES
(
29
,
1
,
NOW
(),
2
),
(
29
,
2
,
NOW
(),
2
),
(
35
,
1
,
NOW
(),
1
),
(
35
,
2
,
NOW
(),
1
),
(
26
,
1
,
NOW
(),
2
),
(
26
,
2
,
NOW
(),
1
),
(
27
,
1
,
NOW
(),
2
),
(
27
,
2
,
NOW
(),
4
),
(
16
,
1
,
NOW
(),
3
),
(
3
,
1
,
NOW
(),
1
),
(
3
,
2
,
NOW
(),
1
);
This isn't many rows, but it's enough for now. Let's count the votes for the first survey
question by executing the following:
SELECT IFNULL(COLUMN_GET(choices, answer AS CHAR), 'total')
AS 'Birding Site', COUNT(*) AS 'Votes'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer WITH ROLLUP;
+--------------+-------+
| Birding Site | Votes |
+--------------+-------+
| forest | 2 |
| shore | 3 |
| backyard | 1 |
| total | 6 |
+--------------+-------+
In the
WHERE
clause,
survey_id
chose the survey we want from
sur-
vey_questions
while
question_id
chose the question we want from
sur-
