Group by, distinct and subdivide by
Queries typically return actual records, with one row for each record, or in a downloaded file being a single record. While that may often be what is required, just as often some sort of summary of the records is required. Rather than having to download records and manipulate them in a spreadsheet, the summarising can much more easily, and speedily be carried out within the DDb. This is done by using options found via more options > grouping.
Group by when used alone, counts the number of records within a category, eg per vice-county, hectad, or tetrad.
When an additional distinct clause is added to a ‘group by’ query, the frequency count returned refers to the number of distinct variants there are of the field specified by the ‘distinct’ clause.
Subdivide by adds columns to the output table, subdividing the records, eg. into date class or year.
Distinct and subdivide by can only be used in combination with group by in a query.
An example will help clarify this.
County Waterford (vcH12) had 242825 records on the DDb (as of October 2023). This is a large dataset, and in its raw form, rather unwieldy to work with. But it can be summarised easily, in a variety of ways to meet the needs of the DDb user.
Adding group by localites > hectad, lists hectads with records in the left hand column.
By default, this will show the frequency of records in each hectad.
We will now shift to displaying the number of distinct species per hectad. We do this by adding a grouping > distinct from the more options menu, and then selecting species from the list of options.
This will count the number of species and hybrids (sub-species are lumped into their parent species) in each hectad, and that figure is displayed in the right hand column in the results table.
Next, we will look at the results over a time period. To do this, we can adding subdivide by date class.
This adds 6 columns to the results table, one for each BSBI date class, and the values are the number of species & hybrids in each hectad in each date class. The first row, with no hectad listed, refers to records for the whole vice-county or other locations, with no grid reference given in the original record.
Join with a sub-query
Sub-queries add considerable sophistication to searches. To create a sub-query, you use more options > join with a sub-query
Next you choose what type of query you want to join with:
Then you select what the two queries will be joined by:
You would use a join in instances where you need to link the results of two separate queries. An example of using a join is searching for a list of infraspecific taxa in a vice county. The first query returns the taxa in the vice county. The second query returns only taxa below the species level. The join links these two queries together.
If you leave join using blank then you can combine the results from your two different query into a single set of results.