Hello dear Jean and all MVD users, and ... happy new year !!
Haaaaa, recursive queries.... "tout un monde !!!" (en français dans le texte). Sorry in advance, this answer might be a little long
To start with, I do not know how you set up you database. I am going to assume that you have at least two tables : one with the butterflies families and one with the butterflies themselves.
What I set up as an example looks like this :
At this stage, and based on your screen-capture, it is not difficult to count, for each family, how many butterflies are attached to it. But if these families are in fact sub-families, how can we count (or sum) how many butterflies there are in each main families ?
Again, in your screen-capture, the Nymphalidés family should count 21, which is the sum of all butterflies belonging to each sub-familes.
For this, you can not use "nodecount" because butterflies or not subnodes of each families, they are in another table.
You will have to use a RECURSIVE query.
In a simple query, you select N fields by their names in a table. In a RECURSIVE query, one (or more) of the fields you will retrieve is not a named field, but a calculated field counting ID from a query in the main query. The "magic" being that the query interrogates itself until no more results are found !! For this, we will create a virtual table stored in memory (I called it IN_MEMORY_TABLE but you can call it whatever you want, the only reserved word being WITH RECURSIVE). This works with Sqlite and is called CTE (common table expressions). You can even solve suddokus with this type of query.
Good link for explanation : https://sqlite.org/lang_with.html
God, I hope I am being clear With an example, things will be simpler.
NOTE : I am not a specialist in butterflies, I merely googled for names (and even invented some) just for the table to look pretty
Based on my tables model (you might have to adapt it to you scheme) the query looks like this :
SELECT DISTINCT
familyID AS FID,
familyParentID AS FPID,
familyName AS FN,
(
SELECT
COUNT( butterflyID )
FROM
butterflies
WHERE
butterflyFamilyID IN (
WITH RECURSIVE IN_MEMORY_TABLE ( familyID ) AS ( VALUES ( butterflyFamilies.familyID ) UNION ALL SELECT butterflyFamilies.familyID FROM butterflyFamilies JOIN IN_MEMORY_TABLE ON butterflyFamilies.familyParentID = IN_MEMORY_TABLE.familyID ) SELECT
familyID AS TREE
FROM
IN_MEMORY_TABLE
)
) AS NB_BUTTERFLIES
FROM
butterflyFamilies
ORDER BY
familyID,
familyName
You see that, the beginning of the query is pretty standard, we are selecting 3 fields from the butterfyFamilies table. The fourth one, the one called NB_BUTTERFLIES, is the one counting butterflies attached to each families, and recursively counting them for each main family.
The result of this query is :
As you can see, the Nymphalidés family now counts 21 members, decomposed into 3 sub-families having each 7, 2 and 12 members.
i hope this helped you a little. Do not hesitate to come back to me if you table structure is different or if anythings is unclear. you will find a zip archive attached with the sqlite3 database I used and the query file if you want to test with my data.
Again happy new year.
Cheers
Mathias
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor