Database Reference
In-Depth Information
first to count the number of votes for all of the choices. We could put that in a subquery,
but let's keep it simpler by executing a SELECT statement first to get that value. We'll
create a user-defined variable in which to temporarily store that number. A user variable is
temporary and will last only for the current client session. It can be accessed only by the
user that creates it. You would usethe SET statement to create a user variable. The vari-
able name must start with @ , followed by the equals sign, and then a value, an expression,
or an SQL statement that will determine the value of the user-defined variable. Let's cre-
ate one now for our example. Enter the following on your MariaDB server:
SET @fav_site_total =
(SELECT COUNT(*)
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1);
SELECT @fav_site_total;
+-----------------+
| @fav_site_total |
+-----------------+
| 86 |
+-----------------+
Because I added plenty more rows to the survey_answers table, this result is now
higher than previously. You'll see that the total is correct in the results of the next ex-
ample. Let's use the variable we created as the denominator for calculating the percentage
of votes for each choice:
SELECT COLUMN_GET(choices, answer AS CHAR)
AS 'Birding Site',
COUNT(*) AS 'Votes',
(COUNT(*) / @fav_site_total) AS 'Percent'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer;
+--------------+-------+---------+
| Birding Site | Votes | Percent |
+--------------+-------+---------+
| forest | 30 | 0.3488 |
| shore | 42 | 0.4884 |
Search WWH ::




Custom Search