cydewaze — 2013-08-20T11:38:43-04:00 — #1
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?
r937 — 2013-08-20T11:43:43-04:00 — #2
GROUP BY proj_year, state
cydewaze — 2013-08-20T12:06:17-04:00 — #3
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
r937 — 2013-08-20T13:34:17-04:00 — #4
always glad to help a fellow CF user
cydewaze — 2013-08-20T14:36:37-04:00 — #5
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.
r937 — 2013-08-20T14:57:27-04:00 — #6