I counted birds on different dates and areas. Some birds got a tracking ID. That gives my a table with this header. t1:

SPECIES | AGE | BIRD_TRACKING_ID | AREA | DATE_1
------------------------------------------------
        |     |                  |      |

Then we observated the birds and made a record of the trees they landed on. Every tree got an unique ID. One bird could have visited none, one ore several trees. That gives me table t2 with this header:

TREE |DESCR  |TREE_ID |BIRD_ID
------------------------------
     |       |        |      

CREATE TABLE birds (
    SPECIES varchar(255),
    AGE varchar(255),
    BIRD_TRACKING_ID varchar(255),
    AREA varchar(255),
    DATE_1 date
);

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')
;


CREATE TABLE trees (
    TREE varchar(255),
    DESCR varchar(255),
    TREE_ID varchar(255),
    BIRD_ID varchar(255)
);

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')
;

What I want is to count all birds species grouped by area and species. That's simple so far. But in a next step I want to get the tree ID from t2. I did this:

select 
  B_spec,
  AMOUNT,
  TREE,
  B_age,
  B_age_juv,
  B_age_ad,
  B_track, 
  B_area, 
  date1
  from(
    select
      SPECIES as B_spec, 
      count(SPECIES) as AMOUNT,
      AGE as B_age,
      count(case when AGE = 'juv' then 1 end) as B_age_juv,
      count(case when AGE = 'ad' then 1 end) as B_age_ad,
      BIRD_TRACKING_ID as B_track, 
      AREA as B_area, 
      DATE_1 as date1,
      group_concat(TREE_ID) as TREE
    from birds
    left join trees t2
    on BIRD_TRACKING_ID = t2.BIRD_ID
    group by B_area, B_spec
    order by B_spec asc
    ) t1

and got that: Obviously it is messed up and the counts are wrong.

What I want is in field TREE all the tree IDs of the trees that has been visited by that bird. Furthermore it would be nice to have a count of a specific bird species in one row and in another the bird that has visited a tree.

For example on area A03 I had 3 eagles. 2 with no tracking ID, one with tracking ID. So the output for the 3 eagles on A03 should be:

B_spec  |TREE                |AMOUNT| B_age_juv   |B_age_ad  |B_area |date1
---------------------------------------------------------------------------
eagle   |null                |2     | 1           |1         |A03    |2021-06-02
eagle   |T_2021_1a,T_2021_5a |1     | 0           |1         |A03    |2021-01-01

The date should be the first date when observed a bird of that species on that specifiic area. Eagles on A3 without a tracking ID were seen on 2021-06-02 and 2021-06-15. So the first row has 2021-06-02 as date1.

The data is not realistic, penguins are flightless. And I can use only SQLite in QGIS.

SQL Fiddle

CodePudding user response：

You should create one more grouping level for the birds without BIRD_TRACKING_ID and use conditional aggregation:

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;

See the demo.