SQL QUERY | Sum Values by Group in Access 2010

I have been working with Access a lot lately. In my last project I was working with a table that contained 1.4 million records with count values by US States. I needed run a graph based on the sum of values for each state.

My original table looked like this:

And so on… All of the way through 1.4 million records. For my graph data, I needed to turn the above table into:

I am a big fan of using the SQL design view in Access as I find it to be more powerful. If you are more comfortable with the Access Query builder GUI, you can swap back after writing your query.

SUM Values by Group in Access 2010 | SQL Query

To sum values by state, here is how I structured my query:

SELECT State AS State, SUM(Value) AS 'SUM of State'
FROM states
GROUP BY State
ORDER BY State

Walkthrough

I SELECTed the column ‘State’ and set label as State, I summed the column ‘Value’ and labeled AS ‘SUM of State’
FROM ‘states’ table
GROUP BY ‘State’ column – this part is key. This is how you group your summed data by state.
ORDER BY ‘State’ column – this part is optional as you can sort any way you choose.

As always comment with questions or suggestions on how to improve.