How can I make MySQL sum the average word counts of several different rows?

Question

Basically, I have a database where entries have unique IDs, but there can be multiple versions of one thing with the same ID (differentiated by a version number that, together with the ID, forms the primary key), and those different versions may have different word counts. As such, on certain pages, I'd like to display the sum of the average word count of several different entries (usually the child nodes of a given node). The first query I tried was this:

SELECT SUM(ROUND(AVG(word_count), 0)) AS word_count FROM woh_content WHERE id IN ($children)

(Note that the $children variable is a string of comma-delimited IDs generated by a PHP function — in stripped-down versions of the above query, that part worked just fine, so I know this isn't a PHP issue.)

This query didn't work, of course (although it's worth noting that several SQL syntax checkers said it was just fine), and so I started digging through Stack Overflow and the MySQL documentation to figure out what needed to be changed. Basically everyone and everything mentioned the HAVING clause, but I couldn't find a good explanation for how exactly it would be used in a situation like this — as far as my understanding of MySQL is taking me, what I need is the WHERE clause. I've even used the same snippet of code for getting data on multiple IDs in other queries. But, for whatever reason, that doesn't work here.

I tried my best to come up with alternative queries using the HAVING clause instead, but none of them were... great. For example...

SELECT SUM(ROUND(AVG(word_count), 0)) AS word_count FROM woh_content GROUP BY id HAVING id IN ($children)

SELECT SUM(word_count) FROM (SELECT AVG(word_count) AS word_count FROM woh_content WHERE id IN ($children) GROUP BY id) AS word_count

And, needless to say, I couldn't get any of them to work either.

Anything that might help me understand this issue better would be very much appreciated.

Answer 1

SELECT SUM(ROUND(AVG(word_count), 0)) AS word_count FROM woh_content WHERE id IN ($children)

here of course you missed, so it will not work. You took AVG from the entire column at once, and it will return one scalar value to you, and you will already take the amount from one number

SELECT ROUND(AVG(word_count), 0) AS avg, SUM(avg) FROM woh_content
    WHERE id IN ($children)
    GROUP BY word_count 


SELECT SUM(ROUND(AVG(word_count), 0)) AS word_count FROM woh_content
    WHERE id IN ($children)
    GROUP BY word_count 

Wrote in a hurry, it would be nice to check, because. I can be wrong. But the bottom line is that first you have to ungroup your records, and the avg function will be the aggregate function for merging identical records on the column specified in group by . But according to your question and example, it's still not clear what you wanted. Because according to the explanation in the group by should be the first half of the composite index, which is directly repeated