SQL GROUP_CONCAT + SUM + AVG [英] SQL GROUP_CONCAT + SUM + AVG

问题描述

SELECT *,
       Group_concat(rating) AS rating_total,
       Sum(rating_total)    AS rating_sum,
       Avg(rating_sum)      AS rating_avg
FROM   ratings
GROUP  BY pid  

由于某种原因，总和和平均值无法执行....如何使该语句起作用?

For some reason the sum and average don't execute....how do you make this statement work?

推荐答案

由于SQL编译查询的方式(由于使用的是GROUP_CONCAT，因此我假设使用MySQL)，因此您不能在order by子句之前引用别名列名. SUM和AVG函数不是过程性的.它们是并行完成的.这意味着它们不能依赖.

Because of the way that SQL compiles the queries (I assume MySQL since you're using GROUP_CONCAT), you cannot reference aliased column names before the order by clause. The SUM and AVG functions are not procedural. They are done in parallel. Which means that they can't be dependent.

第二，GROUP_CONCAT返回一个字符串，而不是数字.您希望如何对SUM/AVG进行处理?本身就是SUM和AVG的评级"列.

Secondly, GROUP_CONCAT returns a string, not a number. How to you hope to SUM/AVG that? Just SUM and AVG the rating column in and of itself.

现在，有了这个，您可以:

Now, given this, you could do:

SELECT
    pid,
    GROUP_CONCAT(rating) AS rating_total,
    SUM(rating) as rating_sum,
    AVG(rating) as rating_avg
FROM
    rating
GROUP BY
    pid

这应该可以为您找到想要的东西.

This should get you what you're looking for.

这篇关于SQL GROUP_CONCAT + SUM + AVG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！