Report: branches, with statistics

De TNG_Wiki
Ir a la navegación Ir a la búsqueda

Description: Branches with statistics like treename, description, how many males, females, unknown sex, which tree etc.

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate


SELECT b.branch AS branche_name, b.description AS Branche_description, "Total unique last names " AS Aspect, COUNT(DISTINCT lastname) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID AND bl.gedcom=p.gedcom)
WHERE bl.persfamID LIKE "I%"
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total families" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
WHERE bl.persfamID LIKE "F%"
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total individuals" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
WHERE bl.persfamID LIKE "I%"
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total living" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID AND bl.gedcom=p.gedcom)
WHERE bl.persfamID LIKE "I%" AND p.living<>0
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total males" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID AND bl.gedcom=p.gedcom)
WHERE bl.persfamID LIKE "I%" AND p.sex="M"
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total females" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID AND bl.gedcom=p.gedcom)
WHERE bl.persfamID LIKE "I%" AND p.sex="F"
GROUP BY b.branch
UNION
SELECT b.branch AS branche_name, b.description AS Branche_description, "Total unknown sex" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchelinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)
INNER JOIN tng_trees AS t ON t.gedcom=b.gedcom
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID AND bl.gedcom=p.gedcom)
WHERE bl.persfamID LIKE "I%" AND p.sex="U"
GROUP BY b.branch
ORDER BY family_tree, branche_name, Aspect;