Tracking Bees With GROUP_CONCAT

12 Aug

A Lesson In Apiculture

Many bees pollinate many flowers but what if we want to know exactly which flowers each bee pollinated? We could, hypothetically speaking, attach a tracking device to each bee, label every possible flower, and collect a data table like below:

bee_idflower_id
b1f1
b1f2
b2f1
b2f3
b3f5
b4f4
b5f2
b5f3
b5f7
b5f9

Every time a bee lands on a flower, that flower’s ID is recorded and if the problem was simpler, such as how many flowers each bee visited, in MySQL we could use:

Giving a result like below:

bee_idcounts
b12
b22
b31
b41
b54

However, this lacks the details of which exact flowers the bees visited.

Introducing GROUP_CONCAT

MySQL offers a seemingly little known function called GROUP_CONCAT which concatenates values in a group.

An almost exact SQL code as before, except the COUNT  function has been replaced with  GROUP_CONCAT  resulting in:

bee_idf_ids
b1f1,f2
b2f1,f3
b3f5
b4f4
b5f2,f3,f7,f9

The essence of the problems this function helps to solve are instances of many-to-many relationships. Additional articles on GROUP_CONCAT are listed below:

MySQL Documentation

The Power Of MySQL’s GROUP_CONCAT

One Reply to “Tracking Bees With GROUP_CONCAT”

Leave a Reply

Your email address will not be published. Required fields are marked *