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!
Comments