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 5 of 5

Thread: Country count

  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Country count

    I have database names and there are column country, name, id
    like this:

    Sweden, John, 1
    Sweden, Carl, 1
    Sweden, Bart, 2
    Sweden, Bart, 3
    Sweden, Benny, 4

    Denmark, Adam, 5
    Denmark, Aden, 5
    Denmark, Paula, 6
    Denmark, Paula, 7

    Germany, Rahel, 8
    Germany, Rahel, 8

    how get:
    Code:
    Sweden 3
    Denmark 2
    Germany 1
    PHP Code:
    SELECT countrycount(*) AS theCount FROM names GROUP BY country ORDER BY theCount DESC 

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    select t.state,count(distinct t.name) from temp_table t
    group by t.state
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT countrycount(distinct name) AS theCount FROM names GROUP BY country ORDER BY theCount DESC 
    result is:

    Code:
    Sweden 4
    Denmark 3
    Germany 1
    because no id
    it does not work:
    PHP Code:
    SELECT countrycount(distinct name and id) AS theCount FROM names GROUP BY country ORDER BY theCount DESC 

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    I looked to quick.
    Explain what 3, in expected result, for sweden means ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    *MAYBE* you are after this?
    Code:
    SELECT S.country, COUNT(  DISTINCT S.minid ) AS theCount
    FROM (
        SELECT country, name, MIN(id) AS minid
        FROM names
        GROUP BY country, name 
        ) AS S
    GROUP BY S.country
    ORDER BY S.country
    ???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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