Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
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
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
