Database Reference
In-Depth Information
In these results, because there was only one sighting of the first two birds, the values for
both fields are the same. But for the
Eurasian Curlew
, you can see that it shows the
farthest north and south that the bird was seen by ourmembers.
Concatenating a Group
There is one moreaggregate function that I want to cover before finishing with them.The
GROUP_CONCAT()
function is not used much, but it can be handy for particular situ-
ations. It's used to concatenate together the values for a group into a comma-separated
list. Without it, you would need to do a subquery and use
CONCAT_WS()
to concatenate
the results of a field.
To list the bird families for a particular order of birds, we could issue asimple
SELECT
statement. Now suppose we want a list of bird orders and bird families together, but we
want one of the fields in the results to contain all of the bird families for each bird order.
That would be cumbersome to do without
GROUP_CONCAT()
. Let's see what it can do
for us, using this supposition. Enter the following on your server:
SELECT bird_orders.scientific_name AS 'Bird Order',
GROUP_CONCAT(bird_families.scientific_name)
AS 'Bird Families in Order'
FROM rookery.bird_families
JOIN rookery.bird_orders USING(order_id)
WHERE bird_orders.scientific_name = 'Charadriiformes'
GROUP BY order_id \G
*************************** 1. row ***************************
Bird Order: Charadriiformes
Bird Families in Order:
Charadriidae,Laridae,Sternidae,Burhinidae,Chionidae,Pluvianellidae,
Dromadidae,Haematopodidae,Ibidorhynchidae,Recurvirostridae,
Jacanidae,Scolopacidae,Turnicidae,Glareolidae,Pedionomidae,
Thinocoridae,Rostratulidae,Stercorariidae,Alcidae
I limited the results to one particular family to save space here. To get lists of orders for
all families, just remove the
WHERE
clause:
SELECT
bird_orders
.
scientific_name
AS
'Bird Order'
,
GROUP_CONCAT
(
bird_families
.
scientific_name
SEPARATOR
', '
)
AS
'Bird Families in Order'
FROM
rookery
.
bird_families
JOIN
rookery
.
bird_orders
USING
(
order_id
)
GROUP BY
order_id
\
G
