Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Oct 2007
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Finding Unique Records Then Count Them

    Ok.
    So I have a database full of people details.

    And I want to extract each unique Nationality, then count how many of the nationality there are.

    Make sense??

    So i end up with a table like:

    English: 23
    French: 36
    German: 2
    etc

    Hope you can help!

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Code:
    SELECT nationality, count(*)
    FROM table
    GROUP BY nationality

  • #3
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    You need to use COUNT() and GROUP BY in SQL.

    Code:
    SELECT COUNT(1) AS number, c.name FROM people p, countries c WHERE p.countries_id = c.id GROUP BY c.id, c.name

  • #4
    New Coder
    Join Date
    Oct 2007
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    is there an easier way of doing this?

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    easier in what way? The answer given is the shortest (in terms of lines of code) and the most straightforward. Understanding aggregate functions (sum, count etc.) and 'group by' will help you a lot in the future, avoiding them isn't really very sensible.
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #6
    New Coder
    Join Date
    Oct 2007
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ok, so i am now able to produce a list of all the nationality's without duplicates.

    How do i get it to count how many of each there are and output that?

    Sorry if this is obvious, i am new to this!

  • #7
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    something along the lines of:
    PHP Code:
    $query 'SELECT nationality, count(*) AS count FROM table GROUP BY nationality';
    $result mysql_query($query) or die(mysql_error());

    while(
    $row mysql_fetch_assoc($result)) {
      echo 
    $row['nationality'].': '.$row['count']."<br />";

    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #8
    New Coder
    Join Date
    Oct 2007
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mate!

    You are a total diamond!
    Got it working thanks to you!

    Cheers

  • #9
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    That's what me and Fumigator also said.

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    There must be an easier way....

  • #11
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    I suppose you could say we forgot to include the ability to copy and paste. Shame on us.


  •  

    Posting Permissions

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