So I have a table that describes which company serve which area (see below).

+-------+---------+------------+----------------+----------+--------------+
| id    | comp_id | origin_zip | origin_country | dest_zip | dest_country |
+-------+---------+------------+----------------+----------+--------------+
|  4955 |    2249 | 92100      | FR             | %        | FR           |
|  8328 |    3014 | 92%        | FR             | 50%      | FR           |
|   388 |      81 | 976%       | FR             | %        | FR           |
|  4842 |    2165 | %          | FR             | %        | FR           |
|  5260 |    2504 | %          | FR             | %        | FR           |
|  4422 |    1773 | 94%        | FR             | 84%      | FR           |
|  5433 |    2671 | %          | FR             | %        | FR           |
|  8889 |    3016 | 04%        | FR             | 04%      | FR           |
|  4877 |    2200 | %          | FR             | %        | FR           |
| 10136 |    3054 | 73%        | FR             | 19%      | FR           |
+-------+---------+------------+----------------+----------+--------------+ 

I'm saving wildcards directly into the table. This way, when a user gives me an adress I can use a query like below :

SELECT * FROM company_area WHERE :user_zip LIKE origin_zip AND :user_country = origin_country;

However, now I want to do some statistic on this table. I want for each administrative area in a country, defined by the two first number in a zip code, to count how many company I have. What I want looks like this :

+----------------+------------+-------+
| origin_country | origin_zip | total |
+----------------+------------+-------+
| FR             | 83%        |  1676 |
| FR             | 84%        |   189 |
| FR             | 85%        |   339 |
| FR             | 86%        |  1953 |
| FR             | 87%        |    24 |
| FR             | 88%        |  2896 |
| FR             | 89%        |  1773 |
| FR             | 90%        |  3023 |
| FR             | 91%        |  3054 |
| FR             | 92%        |   386 |
+----------------+------------+-------+

I want to GROUP BY origin_country, origin_zip but I would like the GROUP BY to match wildcards (%) for grouping somehow.

Is something like that achievable with MySQL ?

0 Answers