By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE wcvp (
`kew_id` TEXT,
`genus` TEXT,
`species` TEXT,
`infraspecies` TEXT,
`taxonomic_status` TEXT NOT NULL DEFAULT 'Accepted'
);
✓
INSERT INTO wcvp (`kew_id`, `genus`, `species`, `infraspecies`) VALUES
('304293-2', 'Quercus', 'robur', null),
('77189540-1', 'Quercus', 'robur', 'broteroana'),
('77189379-1', 'Quercus', 'robur', 'brutia'),
('77189383-1', 'Quercus', 'robur', 'imeretina'),
('60459295-2', 'Quercus', 'robur', 'pedunculiflo'),
('77171868-1', 'Quercus', 'robur', 'robur');
✓
SELECT kew_id, genus, species, infraspecies
FROM wcvp
WHERE genus='Quercus' AND species='robur' AND taxonomic_status='Accepted';
| kew_id | genus | species | infraspecies |
|---|---|---|---|
| 304293-2 | Quercus | robur | null |
| 77189540-1 | Quercus | robur | broteroana |
| 77189379-1 | Quercus | robur | brutia |
| 77189383-1 | Quercus | robur | imeretina |
| 60459295-2 | Quercus | robur | pedunculiflo |
| 77171868-1 | Quercus | robur | robur |
CREATE VIEW my_view AS
SELECT kew_id, genus, species, infraspecies,
CASE
WHEN infraspecies IS NULL
THEN COUNT(infraspecies) OVER (PARTITION BY genus, species)
END number_of_infraspecies
FROM wcvp
WHERE taxonomic_status = 'Accepted';
✓
SELECT kew_id, genus, species, infraspecies, number_of_infraspecies
FROM my_view
WHERE genus = 'Quercus' AND species = 'robur';
| kew_id | genus | species | infraspecies | number_of_infraspecies |
|---|---|---|---|---|
| 304293-2 | Quercus | robur | null | 5 |
| 77189540-1 | Quercus | robur | broteroana | null |
| 77189379-1 | Quercus | robur | brutia | null |
| 77189383-1 | Quercus | robur | imeretina | null |
| 60459295-2 | Quercus | robur | pedunculiflo | null |
| 77171868-1 | Quercus | robur | robur | null |