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:
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
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
Thanks Rudy! My next thing to tackle will be to get the totals for each state to appear in the table, like this:
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.