Code : Tout sélectionner
mysql> SELECT year, COUNT(*) FROM Persons WHERE countryId = 'France' AND year != '0' GROUP BY year;
+------+----------+
| year | COUNT(*) |
+------+----------+
| 1963 | 1 |
| 1966 | 1 |
| 1967 | 1 |
| 1970 | 1 |
| 1971 | 1 |
| 1972 | 1 |
| 1974 | 1 |
| 1975 | 2 |
| 1976 | 1 |
| 1977 | 1 |
| 1979 | 1 |
| 1980 | 4 |
| 1981 | 3 |
| 1982 | 2 |
| 1983 | 6 |
| 1984 | 7 |
| 1985 | 3 |
| 1986 | 8 |
| 1987 | 17 |
| 1988 | 7 |
| 1989 | 9 |
| 1990 | 5 |
| 1991 | 5 |
| 1992 | 8 |
| 1993 | 3 |
| 1994 | 4 |
| 1995 | 1 |
| 2003 | 1 |
+------+----------+
Code : Tout sélectionner
mysql> SELECT countryId, AVG(year+month/12+day/365), COUNT(*) FROM Persons WHERE year != '0' GROUP BY countryId ORDER BY AVG(year+month/12+day/365);
+----------------+----------------------------+----------+
| countryId | AVG(year+month/12+day/365) | COUNT(*) |
+----------------+----------------------------+----------+
| Sweden | 1974.03545333 | 15 |
| Netherlands | 1979.05990714 | 56 |
| Belgium | 1979.47201250 | 16 |
| Spain | 1982.20598750 | 32 |
| United Kingdom | 1982.25139474 | 19 |
| Germany | 1984.01579844 | 64 |
| Philippines | 1985.80318889 | 18 |
| France | 1986.42206476 | 105 |
| Japan | 1986.51288053 | 190 |
| India | 1987.57719149 | 47 |
| Canada | 1987.83682333 | 30 |
| Hungary | 1987.86794507 | 71 |
| USA | 1987.98708191 | 293 |
| Poland | 1988.19257619 | 63 |
| Brazil | 1988.29636154 | 13 |
| Korea | 1989.62330200 | 100 |
| China | 1989.73807407 | 27 |
| Finland | 1990.37108462 | 13 |
+----------------+----------------------------+----------+