-- Places-PeopleCount.sql
/*
2017-04-20 Tom Holden ve3meo
Generates list of places (names reversed) with total number
of people with events in each place.

Requires SQLite Views created by PlaceReverse.sql
*/

DROP VIEW IF EXISTS xPlacePersonView
;
CREATE TEMP VIEW xPlacePersonView
AS
SELECT PlaceID, OwnerID AS PersonID --, EventID
FROM EventTable
WHERE OwnerType = 0

UNION

SELECT PlaceID, Fam.FatherID AS PersonID --, EventID
FROM EventTable E
JOIN FamilyTable Fam
ON E.OwnerID = Fam.FamilyID 
AND E.OwnerType=1

UNION

SELECT PlaceID, Fam.MotherID AS PersonID --, EventID
FROM EventTable E
JOIN FamilyTable Fam
ON E.OwnerID = Fam.FamilyID 
AND E.OwnerType=1
;

-- List Total People by Place
SELECT PRV.PlaceReverse AS Place, COUNT(PPV.PersonID) AS People
FROM xPlaceReverseView PRV
NATURAL JOIN xPlacePersonView PPV
JOIN GroupTable G
ON PPV.PersonID BETWEEN G.StartID AND G.EndID
AND G.GroupID = 5 -- plug in GroupID from GroupTable = LabelID from LabelTable for selected group
GROUP BY PRV.PlaceID 
ORDER BY PRV.PlaceReverse
;