Mysql using sum to percentage

Question

我正试图添加另一列，它给了我一个count vs sum的百分比，所以基本上value 1将有25%，value 2是75%，其他将是100%

+----------+--------------------+-------+------+
| type     | value              | count | sum  |
+----------+--------------------+-------+------+
|      600 | 1                  |     1 |    4 |
|      600 | 2                  |     3 |    4 |
|      601 | 2                  |     1 |    1 |
|      602 | 1                  |     1 |    1 |
|      603 | 8                  |     1 |    1 |

SELECT a1.type, 
    a1.value, 
    COUNT(*) AS count,
     (SELECT SUM(a2.total) AS sum 
        FROM (SELECT a3.type, 
            a3.value, 
            count(*) AS total 
                FROM Answer AS a3 
            GROUP BY 1) AS a2
        WHERE a2.type = a1.type      
    GROUP BY type) AS sum
    FROM Answer a1
GROUP BY 1, 2;

我在添加这部分时遇到了麻烦:

(count / sum * 100 AS percentage)

怎么做?

Answer 1

与子查询连接，而不是使用相关子查询:

SELECT a1.type, a1.value, COUNT(*) AS count, a2.sum, COUNT(*)/sum * 100 AS percentage
FROM Answer a1
JOIN (SELECT type, SUM(total) AS sum
      FROM (SELECT type, value, COUNT(*) AS total
            FROM Answer
            GROUP BY type, value) AS sub1
      GROUP BY type) AS a2
ON a1.type = a2.type
GROUP BY a1.type, a1.value

另一种方法是将原始查询放入子查询中:

SELECT type, value, count, sum, count/sum*100 AS percentage
FROM (<original query>) AS x

您需要这样做，因为您不能在同一个SELECT子句中引用来自表达式的别名。