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:

FirstName	LocationState
Jim	MN
Bob	MN
Kim	IL
Beth	WI
Sam	CA
Al	NY
John	MN
Ryan	CA
Greg	NY
Sara	NY
Jen	CA

The Query:

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

Results:

FirstName	Expr1001
NY	3
MN	3
CA	3
WI	1
IL	1

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:

Location	Count
NY	3
MN	3
CA	3
WI	1
IL	1

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