Group and aggregate f2

Browser
1.5 hours

Turn many rows into a few numbers: count, sum and compare groups of records, and filter the groups. Everything runs in this browser, on the tables the Schema page describes.

Already know this?

For each species with records from more than 25 datasets, show species_id, its records, its datasets, its preserved specimens (basis_of_record is PRESERVED_SPECIMEN) and its records with no year, most records first. Solve it and the module is marked done.

Your answer

Show answer

SELECT species_id,
       count(*) AS records,
       count(DISTINCT dataset_key) AS datasets,
       sum(CASE WHEN basis_of_record = 'PRESERVED_SPECIMEN' THEN 1 ELSE 0 END) AS specimens,
       count(*) - count(year) AS no_year
FROM occurrences
GROUP BY species_id
HAVING count(DISTINCT dataset_key) > 25
ORDER BY records DESC, species_id;

Concept

An aggregate function turns many rows into one value: count(*) counts rows, count(year) counts the rows where year isn't NULL, count(DISTINCT dataset_key) counts different values, and sum, min, max and avg do what they say; all but count(*) skip NULLs. GROUP BY splits the rows into groups, one per distinct value of its columns (NULL makes a group of its own), and the aggregates then run once per group, so every column you select must be grouped or aggregated.

The clauses now run in this order: FROM, WHERE (filters rows), GROUP BY, HAVING (filters groups, so it can use aggregates), SELECT, ORDER BY. CASE WHEN … THEN … ELSE … END computes a value row by row: use it to put rows into buckets, or inside sum to count only some rows, which turns one grouped query into a small cross-tab.

The table is occurrences, the lab sample of open GBIF records (the lab data page says how it was drawn): one row per record of an animal at a place and, usually, a date. basis_of_record says what kind of evidence it is: a person's observation, a camera trap or GPS collar (MACHINE_OBSERVATION), a museum specimen, a tissue sample.

Worked example

How many records does the sample hold per year, and by basis of record? First the whole table in one row:

Query f2-count

Records per year, from 2016 on. WHERE keeps the rows before they are grouped:

Query f2-years

Records by basis of record:

Query f2-basis

Both at once: one row per basis of record, one column per period, each a sum of a CASE that is 1 for the rows in that period. A record with no year falls in no period, so it gets a column of its own.

Query f2-periods

Lab

Which species have more than 500 records? HAVING keeps the groups whose count passes; WHERE couldn't, because it runs before there are counts.

Query f2-over-500 SELECT species_id, count(*) AS records FROM occurrences GROUP BY species_id HAVING count(*) > 500 ORDER BY records DESC, species_id;
The same question for records from 2000 on: WHERE removes the older rows first, then HAVING judges what is left.

Query f2-recent-over-500 SELECT species_id, count(*) AS records FROM occurrences WHERE year >= 2000 GROUP BY species_id HAVING count(*) > 500 ORDER BY records DESC, species_id;
How many datasets contributed, under how many licenses, and from how many countries? count(DISTINCT …) counts each value once and skips NULL, so records with no country (country_code is NULL) don't count as a country.

Query f2-distinct SELECT count(DISTINCT dataset_key) AS datasets, count(DISTINCT license) AS licenses, count(DISTINCT country_code) AS countries FROM occurrences;

Result

The oldest record is from 1599, and some records have no year at all: ask min and count(year) before trusting a date column. Human observations dominate, and almost all of them are recent; most records before 1900 are museum specimens. Many species stop at exactly 2,000 records: that is the sample's cap per species, not a fact about the animals, and step 1's top rows are a tie for that reason. Every dataset in datasets contributed at least one record, and only two licenses remain, because the sample keeps only CC0 1.0 and CC BY 4.0 records.

Exercise

For each basis of record with at least 1,000 records, show basis_of_record, its records, how many species and how many datasets they come from, and how many have no year, largest first.

Your answer

Show answer

SELECT basis_of_record,
       count(*) AS records,
       count(DISTINCT species_id) AS species,
       count(DISTINCT dataset_key) AS datasets,
       count(*) - count(year) AS no_year
FROM occurrences
GROUP BY basis_of_record
HAVING count(*) >= 1000
ORDER BY records DESC, basis_of_record;

Check

Your rows must equal the answer's, column by column, in any row order. "Already know this?" is checked the same way. Check runs the answer on the tables as loaded, so if you changed them in the playground, Reset database first.

Dialects

PostgreSQL: count(*) FILTER (WHERE year < 1900) is the short form of the sum(CASE …) columns; GROUP BY may name an output column or its position, HAVING may not; / between integers drops the fraction (7 / 2 is 3).
DuckDB (this page's engine): also has FILTER, plus GROUP BY ALL (group by every column that isn't aggregated) and aliases in WHERE, GROUP BY and HAVING; / between integers gives a fraction (7 / 2 is 3.5) and // drops it.
SQL Server: no FILTER: write sum(CASE …) as here; GROUP BY takes neither aliases nor positions; count returns int (count_big for more rows); / between integers drops the fraction.