Count(*) in group by

Databases
1

city country continent Seoul Korea Asia Pusan Korea Asia Berlin Germany Europe Hamburg Germany Europe Peking China Asia Shanghai China Asia Marseille France Europe Paris France Europe Vatican Italy Europe Rome Italy Europe Venice Italy Europe Madrid Spain Europe Cairo Egypt Africa Tokyo Japan Asia Nagoya Japan AsiaI have myTable like the above

SELECT country FROM myTable GROUP BY country

The code above produces the result below.

China Egypt France Germany Italy Japan Korea Spain The result has 9countries.

I like to get the number of the countries,i.e, 8.

SELECT count(*)  FROM myTabley

The code above produces “15”. but I want “8”.

The code below is some of my trials for getting “8”, but all failed in getting “8”.

[code]SELECT count(*) FROM myTable group by country

SELECT count(disctinct country) FROM myTable[/code]
How can I get the number of counties “8” with myTable above?

2

This should do:

SELECT COUNT(DISTINCT(country)) FROM myTable
1 Like
3

No need to put () around the columns after DISTINCT

 SELECT COUNT(DISTINCT country) FROM myTable
4 
SELECT COUNT(*) FROM (SELECT country FROM myTable GROUP BY country) AS d
5 
SELECT COUNT(*) FROM (SELECT country FROM myTable GROUP BY country) AS d

As I echo “$d” with the code above, It produces “null value” instead of “8”.
I am afraid what wrong in my applying your code, r937?

7

d is the alias for the rows source, you don’t have to use it
Set another alias for the COUNT(*) instead:

SELECT COUNT(*) as countries_count 
FROM (SELECT country FROM myTable GROUP BY country)
8

Thank you, megazoid, but the code below which is applying your code produces SQL error

$query="SELECT COUNT(*) as countries_count FROM (SELECT country FROM temp GROUP BY country)"; $sql=mysql_query($query); $row=mysql_fetch_assoc($sql); $countries_count= $row['countries_count'];

The error message is in the below.

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ...

How can I modify the code for removing the error?

9

Modify the code to see if there is any SQL errors:

$sql=mysql_query($query) or die(mysql_error());
10

[quote]$sql=mysql_query($query) or die(mysql_error())[/quote];

As I apply your code above, it says the below.

11

Okay, I was wrong :smiley: You have to specify table alias:

SELECT COUNT(*) as countries_count 
FROM (SELECT country FROM myTable GROUP BY country) as d
12

Thankyou, megazoidMentor,

It works fine.

and Thank you, r937.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.