a hard MySQL query...

[pentinat]

I want to display a mysql query that groups all rows by countries, that is easy:

SELECT
country FROM TABLE GROUP BY country

Bu now I want to show as follows the number of rows that includes a grouped country:

USA (5)
Spain (3)
Ireland (9)
......

Hope you understand my question.

Thank you

[[deleted]]

SELECT county,count(*) as number_of_rows FROM table GROUP BY country;

[pentinat]

Thank you, works great!!!

[ciaranmg]

Hi,

This query is of interest to myself also, I wonder could you give a quick explanation of what's going on in it???

Regards
Ciaran

[[deleted]]

It's really nothing special.
"GROUP BY country" tells SQL to run your query once for each unique value of "country" that appears in the table.

Count(*) simply counts how many rows are returned for the current query.

So, in Pepe's example, using the COUNT(*) with the GROUP BY
you can imagine SQL runs queries like:

SELECT country,count() FROM table WHERE country="holland";
SELECT country,count() FROM table WHERE country="spain";
SELECT country,count(*) FROM table WHERE country="germany";

etc for each value of country that is in the table.

Note: "GROUP BY country" does not give the same results as WHERE country="<your value here>". GROUP BY returns informations about the GROUP of rows, not just the rows.

[pentinat]

Ok, let´s make it more complex.

I got the following

Holland (16 records)
Spain (5 records)
England (3 records)
......

Now I have created a new field with "Area" is there any way to display this as follows?

Spain (16 redords)
.Barcelona (8 records)
.Madrid (8 records)
England (3 records)
.London (2 records)
.Manchester (1 record)
..........