SQL GROUP_CONCAT + SUM + AVG

Question

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?

Answer 1

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.

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.

Answer 2

GROUP_CONCAT returns a string, concatenating all the selected valuse. Then, you are trying to sum that string and then you are trying to get the average of the sum.

I think you need to use SUM and AVG upon values from the table, not the result of concatenation.