Question 20786 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get the overall count in a multiple selection system with ranking – Mysql

I am designing a database for a web project in which I am trying to collect users’ emotional feelings toward art collections. A hybrid dual list on the website allows users to select three options from 12 options and then rank the selected three as 1st, 2nd, and 3rd. There are 1000 data points in this project, which means there are 1000 distinct art collections for the users to vote on, each of the art collections has the same 12 options.

collection_poll

+-------+--------------------+  
|   id  |   collection_name  |   
+-------+--------------------+
|   1   |    collection 1    | 
|   2   |    collection 2    | 
|   3   |    collection 3    |  
|   4   |    collection 4    |
|   5   |    collection 5    |
|  ...  |        ...         |
+-------+--------------------+

option

+--------------------+--------------------+----------------+
|   collection_id    |      option id     |      Text      |
+--------------------+--------------------+----------------+
|         1          |          1         |    Emotion 1   |
|         1          |          2         |    Emotion 2   |
|         1          |          3         |    Emotion 3   |
|         1          |          4         |    Emotion 4   |
|         1          |          5         |    Emotion 5   |
|         1          |          6         |    Emotion 6   |
|         1          |          7         |    Emotion 7   |
|         1          |          8         |    Emotion 8   |
|         1          |          9         |    Emotion 9   |
|         1          |          10        |    Emotion 10  |
|         1          |          11        |    Emotion 11  |
|         1          |          12        |    Emotion 12  |
|         2          |          1         |    Emotion 1   |
|         2          |          2         |    Emotion 2   |
|         2          |          3         |    Emotion 3   |
|         2          |          4         |    Emotion 4   |
|         2          |          5         |    Emotion 5   |
|        ...         |         ...        |       ...      |
+--------------------+--------------------+----------------|

vote

+--+-------+-------------+-------------+-------------+-------------+
|id|user_id|collection_id|1st_option_id|2nd_option_id|3rd_option_id|
+--+-------+-------------+-------------+-------------+-------------+
|1 |   1   |      1      |      1      |      8      |      12     |
|2 |   2   |      1      |      3      |      1      |      8      |
|3 |   3   |      1      |      1      |      8      |      3      |
|4 |   1   |      2      |      1      |      8      |      12     |
|5 |   2   |      2      |      3      |      12     |      8      |
|6 |   3   |      2      |      1      |      3      |      12     |
+--+-------+-------------+-------------+-------------+-------------+

This table records each vote and specifies which collection the user votes and the 1st, 2nd, and 3rd options the user ranks.

How do I use MySQL to get this table?

+---------------+-----------+-----------+-----------+-----------+  
| collection_id | option_id | 1st_count | 2nd_count | 3rd_count |
+---------------+-----------+-----------+-----------+-----------+
|       1       |     1     |     2     |     1     |     0     |
|       1       |     2     |     0     |     0     |     0     |
|       1       |     3     |     1     |     0     |     1     |
|       1       |     4     |     0     |     0     |     0     |
|      ...           ...         ...         ...         ...    |
|       1       |     8     |     0     |     2     |     1     |
|      ...           ...         ...         ...         ...    |
|       1       |     12    |     0     |     0     |     1     |
|       2       |     1     |     2     |     0     |     0     |
|       2       |     2     |     0     |     0     |     0     |
|       2       |     3     |     1     |     1     |     0     |
|      ...           ...         ...         ...         ...    |
|       2       |     8     |     0     |     1     |     1     |
|      ...           ...         ...         ...         ...    |
|       2       |     12    |     0     |     1     |     2     |
|      ...           ...         ...         ...         ...    |
+---------------+-----------+-----------+-----------+-----------+

which uses collection_poll.id and option.id to get the result from table_vote.

I have

CREATE
OR REPLACE VIEW "public"."poll_results_first_option_count" AS
SELECT
  vote.collection_id,
  vote.first_option_id,
  count(*) AS first_count
FROM
  vote
GROUP BY
  vote.collection_id,
  vote.first_option_id
ORDER BY
  vote.collection_id,
  vote.first_option_id;

to get the count of each rank but cannot put them together.

Tags:

2

Answers


  1. 0

    Step 1: Do the sums

    SELECT  collection_id,
        1st_option_id,
        COUNT(*) AS 1st_count
    FROM votes
    GROUP BY 1,2

    and do similarly for the others

    Step 2: Put them together (somewhat like "pivot"):

    SELECT  collection_id, 
        1st_count,
        2nd_count,
        3rd_count
    FROM ( ... ) AS t1
    JOIN ( ... ) AS t2  USING(collection_id)
    JOIN ( ... ) AS t3  USING(collection_id)
    ORDER BY collection_id;

    where the "…" comes from step 1

    Login or Signup to reply.
  2. 0

    The accepted answer does not provide the correct results. You need to be LEFT JOINing, starting from your option table and including option_id in the JOIN criteria:

    SELECT  `o`.`collection_id`, `o`.`option_id`,
        `1st_count`,
        `2nd_count`,
        `3rd_count`
    FROM `option` `o`
    LEFT JOIN ( SELECT `collection_id`, `1st_option_id` AS `option_id`, COUNT(*) AS `1st_count` FROM `vote` GROUP BY 1, 2 ) AS `t1` USING (`collection_id`, `option_id`)
    LEFT JOIN ( SELECT `collection_id`, `2nd_option_id` AS `option_id`, COUNT(*) AS `2nd_count` FROM `vote` GROUP BY 1, 2 ) AS `t2` USING (`collection_id`, `option_id`)
    LEFT JOIN ( SELECT `collection_id`, `3rd_option_id` AS `option_id`, COUNT(*) AS `3rd_count` FROM `vote` GROUP BY 1, 2 ) AS `t3` USING (`collection_id`, `option_id`)
    ORDER BY `o`.`collection_id`, `o`.`option_id`;

    In your question you state:

    each of the art collections has the same 12 options

    If this is the case, then there is no need to link them in your option table. Instead you can just have the twelve options (and drop collection_id column) and CROSS JOIN to collection_poll to build the full list:

    SELECT  `cp`.`id`, `o`.`option_id`,
        `1st_count`,
        `2nd_count`,
        `3rd_count`
    FROM `collection_poll` `cp`
    CROSS JOIN `option` `o`
    LEFT JOIN ( SELECT `collection_id`, `1st_option_id` AS `option_id`, COUNT(*) AS `1st_count` FROM `vote` GROUP BY 1, 2 ) AS `t1` ON `cp`.`id` = `t1`.`collection_id` AND `o`.`option_id` = `t1`.`option_id`
    LEFT JOIN ( SELECT `collection_id`, `2nd_option_id` AS `option_id`, COUNT(*) AS `2nd_count` FROM `vote` GROUP BY 1, 2 ) AS `t2` ON `cp`.`id` = `t2`.`collection_id` AND `o`.`option_id` = `t2`.`option_id`
    LEFT JOIN ( SELECT `collection_id`, `3rd_option_id` AS `option_id`, COUNT(*) AS `3rd_count` FROM `vote` GROUP BY 1, 2 ) AS `t3` ON `cp`.`id` = `t3`.`collection_id` AND `o`.`option_id` = `t3`.`option_id`
    ORDER BY cp.id, o.option_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search