Default values for COUNT in MySQL

Question

I have a table looking something like this:

+-------+-----+
|country|prop1|
+-------+-----+
|RO     |  1  |
|RO     |  2  |
|UK     |  1  |
|IT     |  2  |
+-------+-----+

I want to count the rows for which the prop1 is not null and I use the following select:

SELECT `country`, COUNT(*) as number FROM table GROUP BY `country`;

this will return:

+-------+------+
|country|number|
+-------+------+
|RO     |  2   |
|UK     |  1   |
|IT     |  1   |
+-------+------+

however I need the following:

+-------+------+
|country|number|
+-------+------+
|RO     |  2   |
|UK     |  1   |
|IT     |  1   |
|FR     |  0   |
+-------+------+

Do you think something like this can be possible to write directly in SQL? I was thinking something like specifying list of possible values for "country" and a default value (0) if it is not found in the table.

Answer 1

It's not obvious in your example where the FR comes from.

MySQL does not have a list of countries inside it, so country codes should be taken from somewhere.

If you have all countries inside mytable (with prop possibly set to NULL):

SELECT  country, COUNT(prop) as number
FROM    mytable
GROUP BY
        country

If you have countries in a separate table (and a country may be missing in mytable):

SELECT  c.id, COUNT(m.prop) as number
FROM    countries c
LEFT JOIN
        mytable m
ON      m.country = c.id
GROUP BY
        c.id

Answer 2

I think you will have to set up a table with all the countries. It will be best to alter your current table to use the keys of the countries. So that you can do a LEFT JOIN. That will give you all the countries, with NULL values if there are no props for that country.

Answer 3

Thanks for all ¡¡¡ www.tootonga.net