SQLite 3.27

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

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