-- GroupsPopulation.sql
/* 2017-01-03 Tom Holden ve3meo
Returns a list of named groups with the total number
of people in each group. Includes the GroupID which
may be useful for inspecting the GroupTable.

Creates a temporary View which is dropped when the 
SQLite manager closes the database.

Requires the RMNOCASE collation.
*/

-- create a temp view of GroupID and population
DROP VIEW IF EXISTS vGroupTotal
;

CREATE TEMP VIEW vGroupTotal AS
SELECT G.GroupID, COUNT() AS [Total] FROM PersonTable P, GroupTable G
WHERE P.PersonID BETWEEN G.StartID AND G.EndID
GROUP BY G.GroupID
;

-- display the results of the temp view along with the group name
SELECT vG.GroupID AS [GID], Lbl.LabelName AS [Group], vG.[Total]
FROM vGroupTotal vG 
JOIN LabelTable Lbl
ON vG.GroupID = Lbl.LabelValue
ORDER BY [Group]
;  

--end of script