> For the complete documentation index, see [llms.txt](https://docs.greenstand.org/freetown-statistics/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.greenstand.org/freetown-statistics/sqls.md). # SQLs These SQL should provide most of the statistics ### Number of Planters under the CBOS `SELECT ent.id, ent."name", count(pla.id) as "Number of planters" FROM public.planter pla left join public.entity ent on pla.organization_id = ent.id WHERE pla.organization_id in (select entity_id from getEntityRelationshipChildren(178)) group by ent."name";` ### Number of Approved trees by CBO *`SELECT ent."name", count(tre.id) as "Number of captures" FROM public.planter pla left join public.entity ent on pla.organization_id = ent.id left join public.trees tre on pla.id = tre.planter_id WHERE pla.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and tre.approved = true group by ent."name";`* ### SELECT ALL CBO NAMES `select entity."name" from entity`\ `WHERE entity.id in (select entity_id from getEntityRelationshipChildren(178))` ### Total number of captures as of 1st of Sept 2021 (Phase 2) `SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) AND a.time_created > '2021-09-01 00:00:01';` ### Total number of APPROVED Captures until 1st of Sept (Phase1) `SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.approved = true AND a.time_created < '2021-09-01 00:00:01';` ### Total number of rejected Captures `SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.rejection_reason notnull;` ### Number of Species verified `SELECT count(distinct(species_id)) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and a.species_id notnull` ### List of Planters and their associated CBOS also includes total of trees captured, rejected, approved and details (Phase 2) `SELECT p.id, p.first_name, p.last_name, p.phone, count(t.id), p.organization_id, p.organization, count(`*`) AS TOTAL, count(`*`) FILTER (WHERE t.rejection_reason isnull) AS VERIFIED, count(`*`) FILTER (WHERE t.approved = false) AS UNAPPROVED, count(`*`) FILTER (WHERE t.rejection_reason notnull) AS REJECTED FROM public.trees t join planter p on t.planter_id = p.id WHERE p.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and t.time_created > '2021-09-01 00:00:01' group by p.id order by count(t.id) desc;` ### Number of captures marked as dead (apply time filter for Phases) `SELECT count(*) FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) and rejection_reason = 'dead'` ### complete Dataset (filtered by date) `SELECT`\ `b.id as PlanterId, b.first_name, b.last_name, b.organization, a.id as treeID, a.lat, a.lon, a.image_url, a.active, a.planter_identifier, a.note, a.species, a.species_id, a.verified, a.capture_approval_tag, a.rejection_reason, a.time_created FROM trees a INNER JOIN planter b ON a.planter_id = b.id WHERE b.organization_id in (select entity_id from getEntityRelationshipChildren(178)) AND a.time_created BETWEEN '2021-09-01 00:00:01' and '2021-11-01 00:00:01';`