Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Question Unanswered: count no of values that are the same

    I have a DB that stores many values. I need to count how many time the same value appears. ie
    I have a table with a field name "JobNo" which might have the following values
    2
    2
    2
    3
    3
    4
    5
    5
    5
    5
    6
    6
    how would i count the number of 5's for instance usign a query?

    thanks in advance
    Actionant

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select JobNo
         
    count(*) as thecount
      from yourtable
    group
        by JobNo 
    this gives counts for each JobNo

    if you really only want the count for one of them,
    PHP Code:
    select count(*) as thecount
      from yourtable
     where JobNo 

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by r937
    PHP Code:
    select JobNo
         
    count(*) as thecount
      from yourtable
    group
        by JobNo 
    this gives counts for each JobNo

    if you really only want the count for one of them,
    PHP Code:
    select count(*) as thecount
      from yourtable
     where JobNo 

    not really what I wanted but I figured it out anyway.
    thanks
    ActionAnt

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do me, and anyone else who happens to see this thread when searching the forum, a favour and let us know what you really wanted and what your solution was
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by r937
    please do me, and anyone else who happens to see this thread when searching the forum, a favour and let us know what you really wanted and what your solution was
    this is the query I used:
    SELECT Count(JobNo) AS CountOfJobNo, JobNo
    FROM JobEmpJunction
    GROUP BY JobEmpJunction.JobNo;

    this shows the current jobNo, which may be 3 for argument's sake
    and then the count of jobs where the jobno is 3.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow

    well, if you compare your query to the first query i gave you, you will see that the difference is very, very slight -- mine counts rows and yours counts not-null values

    the distinction is really academic

    your query will run more slowly and not return a count for nulls

    your query will not show the "current" JobNo, it will actually show the count of all JobNo values

    well, except for the null ones, of course



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Oh, you're right.
    I see that now.
    thanks for your help.

Posting Permissions

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