SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)

    Return row with most occurances

    Ive got a table with many fields and rows. One field has a name of who will be working a file. Usually 1 of 10 different names will be in this field. I need to find which name appears the most and how many times it does.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT name
         , COUNT(*) AS times
      FROM daTable
    GROUP
        BY name
    ORDER
        BY times DESC
    and then, depending on which database sytem you're using (oracle? sybase? db2? postgresql?) you would alter that query slightly to return only the first row

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Im having trouble with my order by.... heres what i have...
    Code:
    SELECT Count(Table1.assigned) AS Expr1
    FROM Table1
    group by assigned
    order by Expr1 DESC;
    I actually dont need the value of who has the most occurances, just the amount of the most occurances. Im using access and its prompting me to specify the value of exp1?

    Tried this as well form within my vba script:
    Code:
    SELECT count (assigned) as CountOfassigned FROM table1 GROUP BY assigned ORDER BY CountOfassigned DESC //gives me error in sql syntax
     
    SELECT count (assigned) as CountOfassigned FROM table1 ORDER BY CountOfassigned DESC GROUP BY assigned // gives me too many too few paramters error

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I actually dont need the value of who has the most occurances, just the amount of the most occurances.
    Code:
    SELECT MAX(humpty) AS most_occurrences
      FROM ( SELECT assigned
                  , COUNT(*) AS humpty
               FROM Table1
             group 
                 by assigned ) AS dumpty
    r937.com | rudy.ca | 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
  •