Getting totals per year and state

I need to pull totals per state and year from a database. Right now I have a table with the following columns:


Each year can have multiple projects in the same state, so there are a bunch of California, 2006 entries.

I need the results to look like this:

Alaska $99,999
Arizona $99,999
Colorado $99,999
Connecticut $99,999

Alaska $99,999
Arizona $99,999
Colorado $99,999
Connecticut $99,999

I know how to do sums, but not grouped by year AND state. Any tips?

GROUP BY proj_year, state

Oh my god. Well it’s clear that being away from coding for 6 months has sapped what little skills I had from my brain.

Thanks. The final product:

SELECT    SUM(project.funding_amt) AS totals
	, project.proj_year
	, state.state_name
FROM project
	ON state.code = project.state
WHERE funding_amt > 0
GROUP BY proj_year, state.state_name
ORDER BY proj_year, state.state_name

always glad to help a fellow CF user :smiley:

Thanks Rudy! My next thing to tackle will be to get the totals for each state to appear in the table, like this:

California $5.00
California $6.00
California $4.00
California $9.50
Total $24.50
Colorado $3.00
Colorado $2.00
Colorado $7.00
Total $12.00

But I think I have to do that in the code, not the SQL. If there’s a way to do it with the SQL, I’m too brain dead to think of it.

correct :slight_smile: