Mysql tree structure get count of childs with level using recursive query

Question

I am using WITH RECURSIVE using UNION ALL mysql query to get level of each node and it is working fine. Now I just want to get count of childs for each node.

WITH RECURSIVE generation AS 

(SELECT id, name, parent_id, 0 AS generation_number FROM users WHERE parent_id IS NULL
UNION ALL 
SELECT 
child.name, 
child.parent_id
generation_number+1 AS generation_number 
FROM users child 
JOIN generation g ON g.id = child.parent_id ) 

SELECT id, name, parent_id, generation_number FROM generation;

Above MySql query returning below table as result excepting last column i.e. "Count of child". What need to do in same query to get count of (Union All) childs.

id	name	parent_id	generation/level	Count of child
1	A	0	0	2
2	B	1	1	1
3	C	1	1	0
4	D	2	2	2
5	E	4	3	0
6	F	4	3	0

Here is source data for creating table

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `sponsor_id` bigint(20) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('Active','Inactive','Deleted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Active',
  `amount` decimal(8,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `sponsor_id`, `parent_id`, `user_id`, `name`, `email`, `phone`, `password`, `image`, `status`, `amount`, `created_at`, `updated_at`) VALUES
(1, NULL, NULL, NULL, 'a', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:42:09', '2022-10-12 04:42:09'),
(2, NULL, 1, NULL, 'b', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:43:34', '2022-10-12 04:43:34'),
(3, NULL, 1, NULL, 'c', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:43:34', '2022-10-12 04:43:34'),
(4, NULL, 1, NULL, 'e', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(5, NULL, 2, NULL, 'f', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(6, NULL, 2, NULL, 'g', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(7, NULL, 2, NULL, 'h', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(8, NULL, 3, NULL, 'i', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(9, NULL, 3, NULL, 'j', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(10, NULL, 3, NULL, 'k', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:47:31', '2022-10-12 04:47:31'),
(11, NULL, 4, NULL, 'l', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(12, NULL, 4, NULL, 'm', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(13, NULL, 4, NULL, 'n', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(14, NULL, 5, NULL, 'o', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(15, NULL, 5, NULL, 'p', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(16, NULL, 6, NULL, 'q', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(17, NULL, 6, NULL, 'r', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(18, NULL, 7, NULL, 's', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(19, NULL, 8, NULL, 't', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(20, NULL, 8, NULL, 'u', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(21, NULL, 8, NULL, 'v', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(22, NULL, 9, NULL, 'w', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(23, NULL, 9, NULL, 'x', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(24, NULL, 10, NULL, 'y', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23'),
(25, NULL, 10, NULL, 'z', '', '', '', NULL, 'Active', '0.00', '2022-10-12 04:50:23', '2022-10-12 04:50:23');

Provide source data as a fiddle or as CREATE TABLE + INSERT INTO. — Akina
– Akina, Commented Oct 31, 2022 at 11:23
Do you need in immediate childs amount? or in total amount of childs, including childs of childs and up to the end of the tree? — Akina
– Akina, Commented Oct 31, 2022 at 13:23

etsuhisa · Accepted Answer · 2022-10-31 12:04:23Z

3

I think you can simply use COUNT and JOIN.

SELECT g1.id, g1.name, g1.parent_id, g1.generation_number,COALESCE(g2.CountOfChild,0)
  FROM generation g1 LEFT JOIN
    (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
    ON g1.id=g2.parent_id;

answered Oct 31, 2022 at 12:04

etsuhisa

1,7881 gold badge7 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

priti narang Over a year ago

Thanks, looks like it's working fine. Somehow can we manage this within Union All in place of doing one more join?

etsuhisa Over a year ago

I think it is necessary to use COUNT and JOIN even in CTE.

priti narang Over a year ago

Can you please help me in stackoverflow.com/questions/74333566/…

Collectives™ on Stack Overflow

Mysql tree structure get count of childs with level using recursive query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related