CodingForums.com - Country count

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Country count (http://www.codingforums.com/showthread.php?t=283345)

Radeom

12-01-2012 11:26 AM

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 country, count(*) AS theCount FROM names GROUP BY country ORDER BY theCount DESC

BubikolRamios

12-01-2012 12:26 PM

Code:

select t.state,count(distinct t.name) from temp_table t

group by t.state

Radeom

12-01-2012 12:55 PM

PHP Code:


		
			
SELECT country, count(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 country, count(distinct name and id) AS theCount FROM names GROUP BY country ORDER BY theCount DESC

BubikolRamios

12-01-2012 04:48 PM

I looked to quick.
Explain what 3, in expected result, for sweden means ?

Old Pedant

12-01-2012 10:03 PM

*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

???

All times are GMT +1. The time now is 05:33 AM.