MySQL SELECT COUNT GROUP BY

MySQL is one of the most popular open-source database management systems. It’s an RDBMS that’s actively developed and maintained by Oracle. MySQL offers a powerful, fast, and secure data storage system that can work with apps of all sizes. Many programming languages support MySQL integration for developing robust applications.

MySQL uses SQL as the communication medium. This guide will showcase using the COUNT() function with the GROUP BY statement in MySQL.

The COUNT() function

In MySQL, the COUNT() function calculates the number of results from a table when executing a SELECT statement. It does not contain NULL values. The function returns a BIGINT value. It can count all the matched rows or only rows that match the specified conditions. If no row is matched, then COUNT() returns 0.

The COUNT() function comes in multiple structures.

$ COUNT(*)

$ COUNT(<expression>)

$ COUNT( [DISTINCT] <expression> )

As the title suggests, the rest of the guide will use the COUNT() function in various demonstrations. Refer to this guide on the COUNT() function in MySQL.

Using SELECT COUNT GROUP BY

We can combine the COUNT() function with GROUP BY to characterize our data into various groups. In this case, a combo of the same values or column will constitute an individual group.

To demonstrate, here’s a sample table “Workers”.

Now, we’ll SELECT the working areas of the workers from the table Workers and group them by Work_location column, meaning the output will be based on unique locations only.

SELECT Work_location, COUNT(*)

FROM Workers

GROUP BY Work_location;

The result rows are grouped by the Work_location column. Each grouped value also comes with the value of the count based on which the rows are arranged.

Using GROUP BY on multiple columns

In the previous example, we only applied the GROUP BY for a single column, right? It’s possible to group the output by multiple columns.

From the previous example, we can group the workers based on both Work_location and Commission. To do so, add the additional fields after GROUP BY separated by commas.

SELECT Work_location, Commission, COUNT(*)

FROM Workers

GROUP BY Work_location, Commission;

Using GROUP BY with ORDER BY clause

We’ve seen the usage of the clause GROUP BY so far. We can pair it with ORDER BY to get an orderly result.

In MySQL, the ORDER BY clause takes the generated rows and arranges them in ascending or descending order. Here’s a quick example of using ORDER BY to arrange the content of the table Workers in descending order.

$ SELECT * FROM Workers ORDER BY Phone DESC;

To get the list in ascending order, use the following query instead.

$ SELECT * FROM Workers ORDER BY Phone ASC;

You can also apply ORDER BY on multiple columns.

$ SELECT * FROM Workers ORDER BY Name, Work_location DESC;

Before jumping to the next demonstration, I recommend familiarizing yourself with in-depths of the MySQL ORDER BY statement.

Let’s combine this new feature with the previous examples. We’ll order the output in either ascending or descending order based on the counts. Take a look at the following example.

SELECT Work_location, COUNT(*)

FROM Workers

GROUP BY Work_location

ORDER BY 2;

Note that we’ve entered a numeric value for the ORDER BY clause instead of a specific column name. It denotes the second column in the output. Let’s try rearranging the output in descending order.

SELECT Work_location, COUNT(*)

FROM Workers

GROUP BY Work_location

ORDER BY 2 DESC;

Final thoughts

This guide demonstrates the usage of various MySQL queries like the COUNT() function in conjunction with the clause GROUP BY. Combined, these statements can create a useful report on the table records by grouping them into several groups. Using the ORDER BY clause, we can also rearrange the output in ascending or descending order.

Happy computing!