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

Requires SQLite Views created by PlaceReverse.sql
*/

DROP VIEW IF EXISTS xPlaceEventsView
;
CREATE TEMP VIEW xPlaceEventsView
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 Events by Place
SELECT PRV.PlaceReverse AS Place, COUNT(PEV.EventID) AS Events
FROM xPlaceReverseView PRV
NATURAL JOIN xPlaceEventsView PEV
JOIN GroupTable G
ON PEV.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
;