1

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
0
0

You can join the queries as subselects, that first cacluate the data.

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

db<>fiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.