Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    16

    Unanswered: what jobs have pay values for every state (was "Odd SQL Statement")

    I am not sure if this is even feasible to do but I can't figure out the SQL statement if it is. I have a career statistics db with pay data for some states and need to find the jobs that I have paydata for each state. The db structure with examples follows:

    PayData Table
    -id
    -job_id (3021, id for job)
    -state ("Alabama", state name)
    -pay (12,500, pay value for job and state)

    Job Table
    -id (3021, job id)
    -job_name ("Computer Programmer", job title)

    What SQL statement could I use to find the jobs that I have paydata in every state?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by chief7
    What SQL statement could I use to find the jobs that I have paydata in every state?
    Can you describe exactly what you mean by this? There are many ways (I can think of five, but I'm sure that there are more) that this question can be interpreted, and only one of them is likely to be "correct" from an employers or an agencies point of view. Without knowing which of the definitions you are using, we have little chance of guessing which of the answers is correct for your case.

    -PatP

  3. #3
    Join Date
    Dec 2005
    Posts
    16
    i do not have a pay value for every job in every state. some jobs only have pay data for a few states, others do have pay values for each state. i want to query the db to find what jobs have pay values for every state.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select job_id
      from PayData
    group
        by job_id
    having count(distinct state) = 50
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    16
    That was exactly what I needed. I had not used GROUP BY before. I used the following SQL statement to achieve my goal:

    Code:
    SELECT job_id, Count(State) 
    FROM PayData
    GROUP BY job_id
    HAVING (((Count(State))=51));
    Thanks r937!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you guys have 51 states now? whoa, i musta been asleep in geography class -- when is your flag gonna get updated?

    okay, quick, how many provinces and territories in canada? no, it's more than 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    P.S. just out of curiosity, how many rows are in your PayData table?

    please tell me what you get for this query:
    Code:
    select count(*) as X, count(distinct state) as Y
      from PayData
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on what you count, there are often more than 50 states. Most state tables have at least 52, some have hundreds (depending on NAFTA interpretation)... The days of 50 states are closer than the days of 13, but that's a relative kind of thing.

    -PatP

  9. #9
    Join Date
    Dec 2005
    Posts
    16
    I have 50,550 total records in the PayData table and 367 jobs with paydata for each state.

    I couldn't run your query in access. I received the following error:

    Syntax error (missing operator) in query expression 'count(distinct state)'.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    syntax error on COUNT DISTINCT? wtf?

    okay, moving your thread out of the SQL forum and into the Microsoft Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937
    you guys have 51 states now? whoa, i musta been asleep in geography class -- when is your flag gonna get updated?

    okay, quick, how many provinces and territories in canada? no, it's more than 10
    The District of Columbia is often included as a "state," as is Puerto Rico. Depending upon the application the other territories (Marshall Islands, Guam, etc.) may also be included.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i certainly do understand how the number could fluctuate depending on what some individual database designer felt was an expedient definition of "state"

    so the true solution would be
    Code:
    select job_id
      from PayData
    group
        by job_id
    having count(state) 
         = ( select count(distinct state)
               from PayData )
    except, of course, that access will barf on this perfectly good sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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