Let’s say you have a basic survey database table where it records a response in the “response” field that will contain different values depending on the survey questions.
Now, it’s pretty easy to gather the value of each response and tabulate the quantity of each response in a list like so:
“Yes” 50 responses
“Maybe” 5 responses
“No” 12 responses
Pitfalls and misunderstanding of “SUM” and “COUNT” in MySQL
However, I have encountered some confusion between the use of “SUM” and “COUNT” in MySQL queries. “COUNT” just counts the number of instances that fit the query abstraction, it’s a way of selecting some data from the entire pool of data out there. Think of a database as a container filled with…. a bunch of different marbles and coins. Database queries are supposed to find just what you’re looking for in that container. What kind of coins? How many black marbles? That’s a way of being presented a container full of things and only taking out what you fits what you’re looking for.
So, if you are trying to sum up a tabulation of counts, a “sum” of “counts” just won’t work, they will throw a “#1111 – Invalid use of group function” error message from MySQL. Why? Because the values in “response” aren’t numeric and the result from “COUNT” you are trying to accumulatively sum aren’t the result of multiple records (invalid use of group function.)
These are too examples of improper MySQL queries using “sum” and “count”. After encountering the “group” error, some may think all they would need is to group the field they are trying to count:
SELECT response,SUM(COUNT(response)) FROM homepage_surveys WHERE survey_id=2 SELECT response,SUM(COUNT(response)) FROM homepage_surveys WHERE id=2 GROUP BY response
These queries all return the tabulations correctly:
SELECT response,COUNT(response) FROM homepage_surveys WHERE id=2 GROUP BY response SELECT response,COUNT(*) FROM homepage_surveys WHERE id=2 GROUP BY response
By aliasing the result of “COUNT(*)” as “count”, this also orders the results and lists them in descending order:
SELECT response,COUNT(*) as count FROM homepage_surveys GROUP BY response ORDER BY count DESC
