I have two queries that get me a correct result, but when I try to join they give me an incorrect result.
I want to make something like:
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
I make a db fiddle to represent what I want and what I get.
Tables and Data
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);
Statement 1
SELECT parte,
sum(cantidad)
FROM directoscount
WHERE deleted_at IS NULL
GROUP BY parte
parte | sum(cantidad) :----- | ------------: 486298 | 56 488208 | 77
Statement 2
SELECT material,
sum(totalStock)
FROM lx02
WHERE deleted_at is null
GROUP BY material
material | sum(totalStock) :------- | --------------: 486298 | 346 488208 | 74
Combined Statements
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
