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:

[proj_name]
[funding_amt]
[state]
[proj_year]

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:

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

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

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
INNER JOIN state
	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: