Properly tabulating a report of various results from a survey with a MySQL query

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 

About Author:

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Leave a Comment

Your email address will not be published. Required fields are marked *