SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE birds (
    SPECIES varchar(255),
    AGE varchar(255),
    BIRD_TRACKING_ID varchar(255),
    AREA varchar(255),
    DATE_1 date
);
CREATE TABLE trees (
    TREE varchar(255),
    DESCR varchar(255),
    TREE_ID varchar(255),
    BIRD_ID varchar(255)
);
INSERT INTO birds (SPECIES, AGE, BIRD_TRACKING_ID, AREA, DATE_1)
VALUES ('eagle',    'ad',  null,      'A03', '2021-06-02'),
       ('merl',     'ad', 'B_mer_01', 'A03', '2021-07-01'),
       ('owl',      'jv',  null,      'A04', '2021-06-11'),
       ('penguin',  'jv', 'B_pen_21', 'A04', '2021-07-01'),
       ('eagle',    'juv', null,      'A03', '2021-06-15'),
       ('eagle',    'ad', 'B_eag_16', 'A02', '2021-06-02'),
       ('merl',     'ad',  null,      'A03', '2021-08-01'),
       ('owl',      'jv', 'B_owl_01', 'A02', '2021-02-01'),
       ('penguin',  'jv', 'B_pen_23', 'A04', '2021-04-17'),
       ('penguin',  'jv',  null,      'A01', '2021-07-15'),
       ('eagle',    'ad', 'B_eag_23', 'A01', '2021-04-11'),
       ('eagle',    'ad', 'B_eag_11', 'A03', '2021-01-01');
INSERT INTO trees (TREE, DESCR, TREE_ID, BIRD_ID)
VALUES ('oak tree', 'd', 'T_2021_1a', 'B_eag_11'),
       ('birch',    'v', 'T_2021_2a', 'B_mer_01'),
       ('oak tree', 'v', 'T_2021_3a', 'B_owl_01'),
       ('larch',    'v', 'T_2021_4a', 'B_pen_23'),
       ('larch',    'v', 'T_2021_5d', 'B_pen_23'),
       ('birch',    'd', 'T_2021_5a', 'B_eag_11');
SELECT b.SPECIES B_spec, 
       GROUP_CONCAT(t.TREE_ID) TREE,
       COUNT(DISTINCT b.rowid) AMOUNT,
       COUNT(DISTINCT CASE WHEN AGE = 'juv' THEN b.rowid END) B_age_juv,
       COUNT(DISTINCT CASE WHEN AGE = 'ad' THEN b.rowid END) B_age_ad,
       b.AREA B_area,
       MIN(b.DATE_1) date1
FROM birds b LEFT JOIN trees t
ON b.BIRD_TRACKING_ID = t.BIRD_ID
GROUP BY B_area, B_spec, b.BIRD_TRACKING_ID IS NOT NULL
ORDER BY B_spec;


B_spec
TREE
AMOUNT
B_age_juv
B_age_ad
B_area
date1


eagle
null
1
0
1
A01
2021-04-11

eagle
null
1
0
1
A02
2021-06-02

eagle
null
2
1
1
A03
2021-06-02

eagle
T_2021_1a,T_2021_5a
1
0
1
A03
2021-01-01

merl
null
1
0
1
A03
2021-08-01

merl
T_2021_2a
1
0
1
A03
2021-07-01

owl
T_2021_3a
1
0
0
A02
2021-02-01

owl
null
1
0
0
A04
2021-06-11

penguin
null
1
0
0
A01
2021-07-15

penguin
T_2021_4a,T_2021_5d
2
0
0
A04
2021-04-17

B_spec	TREE	AMOUNT	B_age_juv	B_age_ad	B_area	date1
eagle	null	1	0	1	A01	2021-04-11
eagle	null	1	0	1	A02	2021-06-02
eagle	null	2	1	1	A03	2021-06-02
eagle	T_2021_1a,T_2021_5a	1	0	1	A03	2021-01-01
merl	null	1	0	1	A03	2021-08-01
merl	T_2021_2a	1	0	1	A03	2021-07-01
owl	T_2021_3a	1	0	0	A02	2021-02-01
owl	null	1	0	0	A04	2021-06-11
penguin	null	1	0	0	A01	2021-07-15
penguin	T_2021_4a,T_2021_5d	2	0	0	A04	2021-04-17