This article describes how to output data group size counts in MySQL. I was required to carry this out when I needed to see how many items belonged to each group within a large set of data. The data I was looking at was UK company registration data and I wanted to see the number of records that existed for each of the 300 companies in the table. I specifically wanted to know how many companies were located in the United Kingdom.
Here’s the MySQL code I used to produce a list of group size counts based on country for UK registered companies…
select distinct RegAddress_Country, count(*) from companies where RegAddress_Country = group by RegAddress_Country;
If you’re wondering why to get a count of United Kingdom based companies, I didn’t just use…
select count(*) from companies where RegAddress_Country = 'United Kingdom';
…It was because after looking at a sample of the data, I realised that there were some inconsistencies and ambiguities. In the RegAddress_Country column there were 42 different terms which could describe a record for a United Kingdom based company. An extract of the data is provided below which doesn’t include the county names and post codes I also found in the column.
MySQL Group Size Counts for UK company registered Countries.
| BRITAIN | 2 |
| BRITISH CHANNEL ISLANDS | 1 |
| ENCLAND | 1 |
| ENGLAN | 3 |
| ENGLAND | 461335 |
| ENGLAND – UK | 169 |
| ENGLAND. | 2 |
| GB | 36 |
| GBR | 3 |
| GREAT BRITAIN | 937 |
| GREAT BRITIAN | 1 |
| GT BRITAIN | 2 |
| GT BRITTAIN | 1 |
| INITED KINGDOM | 1 |
| NORTHERN IRELAND | 9291 |
| SCOTLAND | 39662 |
| SCOTLAND UK | 327 |
| SCOTLAND UNITED KINGDO | 2 |
| SCOTLAND UNITED KINGDOM | 8 |
| SCOTLAND, | 1 |
| SCOTLAND, U.K. | 2 |
| SCOTLAND, UK | 14 |
| SCOTLAND/UK | 1 |
| U | 2 |
| U K | 17 |
| U KINGDOMNITED | 1 |
| U. K. | 2 |
| U.K | 27 |
| U.K. | 250 |
| UK | 6910 |
| UK. | 1 |
| UNITED KIGDOM | 1 |
| UNITED KINGDOM | 554641 |
| UNITED KINGDOMS | 1 |
| UNITED KINGDON | 9 |
| UNITED KINGOM | 1 |
| UNITED KNGDOM | 1 |
| UUNITED KINGDOM | 1 |
| WALES | 14575 |
| WALES UK | 5 |
| WALES, U.K. | 1 |
| WALES, UK | 1 |