How to Count by Item With SQL | Group By Query MS Access

I also wasn’t sure how to word the title, but it seems to make sense. This is a very short post as this is pretty simple.

When reporting out on data, there is the possibility that at some point, a report request will call for a count of items by group. I wanted to make this example as simple as possible. To keep it simple, I designed a table with first names(FirstName) and location states (LocationState).

SQL Query Count Names by State

Here is the raw data:

The Query:

SELECT LocationState, COUNT(FirstName)
FROM names
GROUP BY LocationState
ORDER BY Count(FirstName) DESC;

Results:

If you want to make your results a little more fancy by adding custom column names:
NOTE: The syntax for changing column text in MS Access is to use brackets “[]”. The syntax with other database packages may use quotations. A best practice would be to search Google for the correct syntax based on your database service.

SELECT LocationState AS [Location], COUNT(FirstName) AS [Count]
FROM names
GROUP BY LocationState
ORDER BY Count(FirstName) DESC;

Results:

If you get stuck, comment. If this was helpful, feedback is also welcomed!