Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Count Query

  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Unanswered: Count Query

    Hi

    I have a simple table:

    Person {firstname, surname, age, hair color}.

    How do I query for the most popular firstname?

    ive got:
    Code:
    SELECT firstname, count( firstname )
    FROM person
    GROUP BY firstname
    this displays all the firstnames and the number of times it appears which is fine, but i just want the most submitted firstname to appear. any ideas? would appreciate if somebody could direct me into the right direction.
    thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16
    Try adding this after your group by clause:

    order by count(firstname) desc limit 1

  3. #3
    Join Date
    Nov 2006
    Posts
    20
    thanks for reply.
    but if i add that it says the following:
    #1111 - Invalid use of group function

  4. #4
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16
    What version are you using? I'm not sure whether pre 5.0.x version support group by or limit. Thats before my time. I've gotta run now, but check the syntax for the select statement from the manual for your version to see if you can do this.

    I even ran a test on my dev server:

    mysql> select name, count(*) from jd_test group by name order by count(*) desc limit 1
    -> ;
    +----------+----------+
    | name | count(*) |
    +----------+----------+
    | whatever | 3 |
    +----------+----------+
    1 row in set (0.04 sec)

    mysql> select * from jd_test ;
    +------+----------+
    | id | name |
    +------+----------+
    | 1 | whatever |
    | 1 | whocares |
    | 1 | ido |
    | 1 | whatever |
    | 1 | whatever |
    | 1 | whocares |
    +------+----------+
    6 rows in set (0.00 sec)

    mysql>

  5. #5
    Join Date
    Nov 2006
    Posts
    20
    nope saying the message, think it may be because im using mysql version 4.1. but surely there must be an alternative for me

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    version 4.1 does support GROUP BY and LIMIT

    please show your exact query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16
    I haven't used 4.1 but the online manual for that says you can use limit / group by.

    Try qualifying the "count" column:

    SELECT firstname, count(firstname) as FirstNameCount
    FROM person
    GROUP BY firstname
    order by FirstNameCount desc limit 1


    This works on my 5.1 server.

  8. #8
    Join Date
    Nov 2006
    Posts
    20
    thanks qualifiying the column seemed to work. The query works but..

    it returns Daniel as the most popular as it appears 3 times in that column,
    however the names John and Lee also appear 3 times. is it going to be more complex to return all the popular names not just the first one by alphabetical order?
    thanks again

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by monkeymafia
    is it going to be more complex to return all the popular names not just the first one by alphabetical order?
    the answer is yes, it is

    too bad mysql's LIMIT does not work the same as microsoft's TOP n WITH TIES

    try this --
    Code:
    select firstname
         , names
      from (
           select firstname
                , count(firstname) as names
             from person 
           group
               by firstname
           )  as T
     where (
           select count(*)
             from (
                  select firstname
                       , count(firstname) as names
                    from person
                  group 
                      by firstname
                  ) as T2
            where names > T.names ) < 1
    Last edited by r937; 12-05-07 at 14:00.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2006
    Posts
    20
    hmm, I think you may have understood and i didnt explain it very well :P
    I just changed the limit to 3 instead of 1. does what i need it to now.
    thanks for help guys.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    may i ask why you chose LIMIT 3?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2006
    Posts
    20
    Quote Originally Posted by r937
    may i ask why you chose LIMIT 3?
    because there are 3 names that are equally popular in the table. out of interest what was that query trying to achieve that you posted above?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what if there are four names that are the most popular? your LIMIT 3 query will be wrong

    out of interest, the query i gave will produce the most popular names whether there is one or many with the same top score
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2006
    Posts
    20
    ohh i see, that does make a whole lot more of sense
    however , when i attempt to run that query im given this error:
    #1248 - Every derived table must have its own alias

    which i found out that means each subquery must contain unique column names.
    I tried changing the names but still no luck. has it got something to do with "names" on the 2nd line of the query.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, actually, that message refers to subqueries acting as derived tables

    i've edited my previous post and added the table alias in red
    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
  •