SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    GROUP BY proj_year, state
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code SQL:
    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
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    always glad to help a fellow CF user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    But I think I have to do that in the code, not the SQL.
    correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •