Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    San Antonio
    Posts
    20

    Unanswered: SQL and Frequency

    If I have a table with a First_Name and Last_Name field, how would I
    create a SQL atatement which returns the most frequent first name?

  2. #2
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: SQL and Frequency

    Something like this:

    Code:
    SELECT TOP 1 Count(Namelist.FirstName) AS CountOfFirstName, Namelist.FirstName, Namelist.LastName
    FROM Namelist
    GROUP BY Namelist.FirstName, Namelist.LastName
    ORDER BY Count(Namelist.FirstName) DESC;
    When you are in the query builder click the Sum button (the zigzaggy 'E'). This gives you options for grouping, summing, counting averaging etc.

    Brian

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brian, you cannot group by both first and last and still get the most frequently occurring first name

    you want to group only by first name


    rudy

  4. #4
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59
    Thanks Rudy,

    That would explain why my test only turned up 6 'James' out of 16,000 records. They were all 'Smith'. Leave out LastName and I get 562 'James'.

    Just moving a little too fast this morning,

    Brian

Posting Permissions

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