mysql select sum if value equal set sum to 0

I am developing wms software, I write SQL to calculate items stock in warehouse. in the table I record out and in stock values if go out then negative value if receive in the warehouse item value is positive. In the same table I record inventory control too. So, I need SQLquery with sum all items stock and if have inventory control with same id of items, then if found SUM expression an inventory control row , The SUM expression need to equal with inventory control value and the resume summing.

So when SUM expression run into control row, I need to sum from the latest control to a defined date

information: -- Server version: 8.0.18 -- PHP Version: 7.3.11

Create table :

CREATE TABLE magacin_zalihe ( magacin_zalihe_id int(11) NOT NULL, magacin_zalihe_magacin varchar(8) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_materijali_id int(11) NOT NULL, magacin_zalihe_materijali_sifra varchar(100) COLLATE utf8mb4_general_ci NOT NULL, magacin_zalihe_materijali_naziv varchar(100) COLLATE utf8mb4_general_ci NOT NULL, magacin_zalihe_materijali_kolicina decimal(10,2) NOT NULL, magacin_zalihe_materijali_tip varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_materijali_tip2 varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_materijali_mera varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_materijali_pakovanje decimal(10,2) NOT NULL, magacin_zalihe_materijali_paleta decimal(10,2) NOT NULL, magacin_zalihe_materijali_tezina decimal(10,2) NOT NULL, magacin_zalihe_materijali_opis varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_magacioner varchar(100) COLLATE utf8mb4_general_ci NOT NULL, magacin_zalihe_datum date NOT NULL, magacin_zalihe_narudzba varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, magacin_zalihe_vrsta varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_kor_modif varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, magacin_zalihe_dat_modif timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT:

INSERT INTO magacin_zalihe (magacin_zalihe_id, magacin_zalihe_magacin, magacin_zalihe_materijali_id, magacin_zalihe_materijali_sifra, magacin_zalihe_materijali_naziv, magacin_zalihe_materijali_kolicina, magacin_zalihe_materijali_tip, magacin_zalihe_materijali_tip2, magacin_zalihe_materijali_mera, magacin_zalihe_materijali_pakovanje, magacin_zalihe_materijali_paleta, magacin_zalihe_materijali_tezina, magacin_zalihe_materijali_opis, magacin_zalihe_magacioner, magacin_zalihe_datum, magacin_zalihe_narudzba, magacin_zalihe_vrsta, magacin_zalihe_kor_modif, magacin_zalihe_dat_modif) VALUES (24, '1000', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '200.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-20', '', 'utovar', 'rsolar', '2021-05-20 12:26:50'), (25, '1000', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '250.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-20', '', 'utovar', 'rsolar', '2021-05-20 12:26:50'), (26, '1000', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '50.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-20', '', 'utovar', 'rsolar', '2021-05-20 12:26:50'), (27, '1000', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '-50.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'rsolar', '2021-05-19', '', 'istovar', 'rsolar', '2021-05-21 05:56:59'), (28, '1000', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '-25.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-19', '', 'istovar', 'rsolar', '2021-05-21 05:56:59'), (29, '1000', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '100.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'rsolar', '2021-05-21', '', 'utovar', 'rsolar', '2021-05-21 08:03:28'), (30, '1000', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '50.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'rsolar', '2021-05-22', '', 'utovar', 'rsolar', '2021-05-21 08:05:10'), (31, '1000', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '-100.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'dbence', '2021-05-19', '', 'istovar', 'dbence', '2021-05-21 08:25:14'), (32, '1000', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '-80.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'dbence', '2021-05-21', '', 'istovar', 'dbence', '2021-05-21 08:31:13'), (33, '1000', 52, '1122432441001', 'FIMAL A4/P', '-80.00', '2', '0', 'm2', '7.50', '20.00', '30.00', '', 'dbence', '2021-05-21', '', 'istovar', 'dbence', '2021-05-21 08:31:13'), (34, '100', 125, '1124331321001', 'POLIFLEX PF4/S', '-100.00', '2', '0', 'm2', '10.00', '23.00', '44.00', '', 'rsolar', '2021-05-21', '', 'istovar', 'rsolar', '2021-05-21 08:57:27'), (35, '1000', 121, '1123320261001', 'POLIFLEX P2', '-100.00', '2', '0', 'm2', '10.00', '30.00', '28.00', '', 'rsolar', '2021-05-21', '123456-2021', 'istovar', 'rsolar', '2021-05-21 12:09:24'), (36, '1000', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '-400.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-21', '223564-2021', 'istovar', 'rsolar', '2021-05-21 12:10:11'), (37, '126', 4, '1111152042050', 'BÁZIS FATA CSUPASZ', '100.00', '2', '0', 'm2', '20.00', '64.00', '9.00', '', 'rsolar', '2021-05-21', '', 'utovar', 'rsolar', '2021-05-21 12:51:55'), (38, '100', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '10.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-26', '', 'utovar', 'rsolar', '2021-05-31 10:18:58'), (39, '100', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '-100.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-31', '', 'istovar', 'rsolar', '2021-05-31 11:15:15'), (40, '100', 24, '1110110002001', 'CARTABIT 350 1000M', '-150.00', '2', '0', 'm2', '1050.00', '1.00', '380.00', '', 'rsolar', '2021-05-31', '', 'istovar', 'rsolar', '2021-05-31 11:16:30'), (41, '100', 131, '1111311852001', 'POLYESTER 260 P11', '10.00', '2', '0', 'm2', '13.50', '36.00', '25.90', '', 'rsolar', '2021-06-23', '', 'utovar', 'rsolar', '2021-07-05 06:12:45'), (42, '100', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '200.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-06-23', 'INV - 2021-06-23', 'inventar', 'rsolar', '2021-07-05 06:12:45'), (43, '100', 31, '1111141002007', 'CARTONFELTRO BITUMATO T350 2x1000M', '100.00', '2', '0', 'm2', '1050.00', '2.00', '370.00', '', 'rsolar', '2021-06-23', '', 'utovar', 'rsolar', '2021-07-05 06:12:45'), (44, '100', 132, '1110212612001', 'POLYSTICK IR-XE 36" IT GRANULAR', '-85.00', '2', '0', 'm2', '18.28', '20.00', '23.50', '', 'rsolar', '2021-05-31', '', 'istovar', 'rsolar', '2021-07-05 06:13:07'), (45, '100', 29, '1111141002005', 'CARTONFELTRO BITUMATO T 300 MARCEGAGLIA', '-1.00', '2', '0', 'm2', '1020.00', '1.00', '346.00', '', 'rsolar', '2021-05-31', '', 'istovar', 'rsolar', '2021-07-05 06:13:07'), (46, '100', 120, '1124314522021', 'POLIFLEX MINERAL P4,5', '100.00', '2', '0', 'm2', '10.00', '23.00', '43.00', '', 'rsolar', '2021-07-05', '', 'utovar', 'rsolar', '2021-07-06 07:49:55');

First column as primary key and auto increment

needed output:

Output

my sql query:

SELECT SUM( t1.magacin_zalihe_materijali_kolicina ) AS temp,t1.* FROM magacin_zalihe AS t1 WHERE t1.magacin_zalihe_magacin='100' AND t1.magacin_zalihe_datum <= '2021-07-07' GROUP BY t1.magacin_zalihe_materijali_id

t1.magacin_zalihe_vrsta contains 1 row with value 'inventar' this is control for warehouse. From this value needed to sum stock values. If have more than 1 row of control we needed to sum from latest control row.

Thanks,

Robert