MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.4.21-MariaDB


CREATE TABLE `directoscount` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `reg` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parte` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ubiclinea` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tramo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brazo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `codigo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uso` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `awcm` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ruta` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numtarjetas` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `surtido` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parada` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `familia` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `plataforma` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `auditoria` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descripcion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numConteo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cantidad` int(255) DEFAULT NULL,
  `counter` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ifa` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `directoscount`
--

INSERT INTO `directoscount` (`id`, `reg`, `parte`, `ubiclinea`, `tramo`, `brazo`, `codigo`, `uso`, `awcm`, `ruta`, `numtarjetas`, `surtido`, `parada`, `familia`, `plataforma`, `auditoria`, `descripcion`, `numConteo`, `cantidad`, `counter`, `ifa`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, '106500', '486298', 'LEC 060F', 'CONTROL BOX', 'C', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 12, 'Cesar', 'IFA 2021', '2021-08-09 19:07:05', '2021-08-09 19:07:26', '2021-08-09 19:07:26'),
(2, '106500', '486298', 'LEC 060F', 'CONTROL BOX', 'C', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .750 TR CR A', '2', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:07:26', '2021-08-09 19:07:26', NULL),
(3, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:09:38', '2021-08-09 19:09:53', '2021-08-09 19:09:53'),
(4, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '2', 23, 'Cesar', 'IFA 2021', '2021-08-09 19:09:53', '2021-08-09 19:14:06', '2021-08-09 19:14:06'),
(5, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '3', 23, 'Cesar', 'IFA 2021', '2021-08-09 19:14:06', '2021-08-09 19:14:06', NULL),
(6, '109458', '486298', 'LEA 080F', 'CRISPERS', 'A', 'T98(2)', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:17:26', '2021-08-09 19:17:26', NULL),
(7, '106506', '488208', 'LEC 070F', 'CONTROL BOX', 'C', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .813 HW A', '1', 20, 'Cesar', 'IFA 2021', '2021-08-09 19:18:02', '2021-08-09 19:19:02', '2021-08-09 19:19:02'),
(8, '106506', '488208', 'LEC 070F', 'CONTROL BOX', 'C', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .813 HW A', '2', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:19:02', '2021-08-09 19:19:02', NULL),
(9, '107150', '488208', 'LED 070F', 'CONTROL BOX', 'D', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '1', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:19:51', '2021-08-09 19:21:50', '2021-08-09 19:21:50'),
(10, '107150', '488208', 'LED 070F', 'CONTROL BOX', 'D', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '2', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:21:50', '2021-08-09 19:21:50', NULL),
(11, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '1', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:22:13', '2021-08-09 19:22:28', '2021-08-09 19:22:28'),
(12, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '2', 44, 'Cesar', 'IFA 2021', '2021-08-09 19:22:28', '2021-08-09 19:23:12', '2021-08-09 19:23:12'),
(13, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '3', 44, 'Cesar', 'IFA 2021', '2021-08-09 19:23:12', '2021-08-09 19:23:12', NULL);

CREATE TABLE `lx02` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `material` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `materialDescripcion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bUn` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `totalStock` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `storageBin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `lx02` (`id`, `material`, `materialDescripcion`, `bUn`, `totalStock`, `type`, `storageBin`, `created_at`, `updated_at`, `deleted_at`) VALUES
(6379, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6380, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6381, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6382, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6383, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6384, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6385, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6386, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6387, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6388, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6389, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6390, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6391, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6392, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6393, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6394, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6395, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6396, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6397, '486298', '8-15 X .750 TR CR A', 'EA', '18,000', 'PTE', 'PS3Q2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6398, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6399, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6400, '486298', '8-15 X .750 TR CR A', 'EA', '12,000', 'PTE', 'PS3O3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6401, '486298', '8-15 X .750 TR CR A', 'EA', '15,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6402, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6403, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6404, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6405, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6406, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6407, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6408, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6409, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6410, '486298', '8-15 X .750 TR CR A', 'EA', '18,000', 'PTE', 'PS6J5', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6411, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6412, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6413, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6414, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6415, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6416, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6417, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6418, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6419, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6420, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6421, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6422, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6423, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6424, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6425, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6426, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6427, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(13008, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'STP', '34145858', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(13009, '486298', '8-15 X .750 TR CR A', 'EA', '1,260', 'STP', '34145857', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(13010, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'STP', '34145856', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(6428, '488208', '8-15 X .813 HW A', 'EA', '1,000', 'PTE', 'SJTA3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6429, '488208', '8-15 X .813 HW A', 'EA', '1,000', 'PTE', 'SJTA3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6430, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6431, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6432, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6433, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6434, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6435, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6436, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6437, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6438, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6439, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6440, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6441, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL);
select parte, sum(cantidad) from directoscount WHERE deleted_at is null group by parte


parte
sum(cantidad)


486298
56

488208
77


select material, sum(totalStock) from lx02 where deleted_at is null group by material


material
sum(totalStock)


486298
346

488208
74


SELECT directoscount.parte, SUM(directoscount.cantidad), lx02.material, SUM(lx02.totalStock) FROM directoscount LEFT JOIN lx02 ON directoscount.parte = lx02.material WHERE directoscount.deleted_at IS null AND lx02.deleted_at IS null GROUP BY directoscount.parte, lx02.material


parte
SUM(directoscount.cantidad)
material
SUM(lx02.totalStock)


486298
2912
486298
1038

488208
1078
488208
222


select
*
FROM
(select parte, sum(cantidad) from directoscount WHERE deleted_at is null group by parte) t1
LEFT JOIN
(select material, sum(totalStock) from lx02 where deleted_at is null group by material) t2
ON t1.parte = t2.material


parte
sum(cantidad)
material
sum(totalStock)


486298
56
486298
346

488208
77
488208
74