Clever Geek Handbook

Default values ​​for COUNT in MySQL

I have a table that looks something like this:

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

I want to count rows for which prop1 is not null and I am using 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 you can write something like this directly in SQL? I was thinking something like specifying a list of possible values ​​for "country" and a default value (0) if not found in the table.

+2


source to share


2 answers


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

.

MySQL

does not have a list of countries within it, so the country codes must 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 the country may not be 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
+4


source


I think you will have to create a table with all countries. Your best bet is to modify the current table to use country keys. So you can do LEFT JOIN. This will give you all countries with NULL values ​​if there is no props for that country.



+1


source






More articles:


All Articles