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!
Leave a Reply