MySQL: Group By / count question

Posted on 2014-07-16
Last Modified: 2014-07-16
Hi All,

Table Name:  table1
Fields:  id, firstname, surname

How can I count the number of times each surname appears?

something like...

SELECT surname, count(surname) As "count" FROM table1 GROUP BY surname

example results would be

Surname      count(surname)
Smith             10
Jones              7
Archer            2

Many thanks
Question by:detox1978
    LVL 58

    Accepted Solution

    You basically have it - no speech marks

    SELECT surname, count(surname) AS surname_total FROM table1 GROUP BY surname
    LVL 2

    Author Closing Comment

    thanks.  didnt think i was too far away  :-)

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now