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.

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');

    

CodePudding user response：

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;