WITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='1') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='2') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='3') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='4') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='5') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='6') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='7') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='8') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='9') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='10') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='11') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naamWITH s AS (select distinct s.*, gj.class, gj.groupjttid, gj.naam as groepnaam,gj.icon as groepicon from taxa.species s join taxa.specieslinks sl on s.speciesid = sl.speciesid join taxa.groups g on sl.groupid = g.groupid join taxa.groupslinks l on g.groupid = l.groupid join taxa.groupsjtt gj on l.groupjttid = gj.groupjttid where gj.groupjttid='12') select d.dataid, d.speciesid, d.aantal, d.tekst, d.remarkable, d.sightingtype, d.lifestage, d.activity, d.subjecttype, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, d.hulp_status from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.countid='247267' and hulp_status>=0 union all select null::bigint, d.speciesid, NULL, null, d.remarkable, min(d.sightingtype), null, null, null, s.class, s.groupjttid, s.scientific, coalesce(NULLIF(s.naam,''),s.name) as naam, groepnaam as groepnaam, groepicon, s.identity, null from counts c join data d on c.countid = d.countid join s on d.speciesid = s.speciesid where c.location_id=8776 and week=24 and jaar=2019and d.speciesid not in (select speciesid from data where countid='247267') group by 1,2,4,5,7,8,9,10,11,12,13,14,15,16 order by groupjttid, naam Jaarrond Tuintelling