### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Count number of elements in root groups via single query?  (Read 586 times)

• Sr. Member
• Posts: 476
##### Count number of elements in root groups via single query?
« on: September 16, 2019, 09:47:06 am »
I have MySQL 5.5 database. It has structure like this:
 ID PARENT_ID NAME EL_TYPE 1 0 Some root group Group 2 1 Second level group Group 3 2 Some element Element
What I need - to count total number of elements in every root group. So, perfect result would look like this:
 NAME COUNT Some root group 1
So, in fact, I need to go from element to root group or from root groups to elements recursively. I know, how to do it via Pascal code. But may be there is some easy way to do it via some single SQL query? Via using WHILE for example.
« Last Edit: September 16, 2019, 09:54:12 am by Mr.Madguy »
DynamicData 3.0 is released!
Since now development is frozen - only optimization passes will be made at some point.
Lack of multiple inheritance turns it into abomination.

#### Zvoni

• Sr. Member
• Posts: 345
##### Re: Count number of elements in root groups via single query?
« Reply #1 on: September 16, 2019, 11:09:08 am »
Going by the sparse data you've given us, i take it that a root group has ParentID=0 (as in: No Parent)
Why not just
SELECT Name, Count(*) FROM MyTable WHERE ParentID=0 GROUP BY Name
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

#### marcov

• Global Moderator
• Hero Member
• Posts: 8024
##### Re: Count number of elements in root groups via single query?
« Reply #2 on: September 16, 2019, 11:26:02 am »
(I think this is a tree like structure mapped on the tabel. The example 1,2,3 all belong to the root group since they reference eachother   3->2->1->x)

#### Zvoni

• Sr. Member
• Posts: 345
##### Re: Count number of elements in root groups via single query?
« Reply #3 on: September 16, 2019, 11:36:34 am »
(I think this is a tree like structure mapped on the tabel. The example 1,2,3 all belong to the root group since they reference eachother   3->2->1->x)

Crap. And he needs the count of elements, in his example that would be ID=3 *sigh*
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

#### Abelisto

• Jr. Member
• Posts: 90
##### Re: Count number of elements in root groups via single query?
« Reply #4 on: September 16, 2019, 11:50:58 am »
Usually it solved by recursive CTE, but MySQL introduced this feature only in 8.0 version.
http://www.mysqltutorial.org/mysql-recursive-cte/
OS: Linux Mint + MATE, Compiler: FPC trunk (yes, I am risky!), IDE: Lazarus trunk

#### Martin_fr

• Hero Member
• Posts: 6049
##### Re: Count number of elements in root groups via single query?
« Reply #5 on: September 16, 2019, 12:08:38 pm »
If you can change the way you store the data: https://en.wikipedia.org/wiki/Nested_set_model

However, this means more work for each insert, as for each insert many rows need to be updated.

#### valdir.marcos

• Hero Member
• Posts: 967
##### Re: Count number of elements in root groups via single query?
« Reply #6 on: September 16, 2019, 05:55:31 pm »
I have MySQL 5.5 database. It has structure like this:
 ID PARENT_ID NAME EL_TYPE 1 0 Some root group Group 2 1 Second level group Group 3 2 Some element Element
What I need - to count total number of elements in every root group. So, perfect result would look like this:
 NAME COUNT Some root group 1
So, in fact, I need to go from element to root group or from root groups to elements recursively. I know, how to do it via Pascal code. But may be there is some easy way to do it via some single SQL query? Via using WHILE for example.
Although not always the best peformance option, using database stored procedure is generally easy for programmers.
CTE from MySQL 8+ would be your best solution.

https://stackoverflow.com/questions/8869402/sql-recursive-select-all-childs-from-a-given-node-in-a-tree
https://stackoverflow.com/questions/1323245/sql-recursion-without-recursion
https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159
https://riptutorial.com/sql/example/11142/recursive-joins
https://www2.cs.duke.edu/courses/spring04/cps216/lectures/rec-02-recursion.pdf
https://dnhome.wordpress.com/2014/04/04/sql-recursive-select-parent-child/
https://medium.com/@CodyEngel/recursive-queries-in-sql-95af9f9fd89c