mysql – How to do left join , same table with different conditions per column


You can move them together like here

Schema (MySQL v8.0)

CREATE TABLE polizamovtos  (
  `idlote` VARCHAR(10),
  `cargo` VARCHAR(10),
  `abono` INTEGER,
  `fechamovto` VARCHAR(10)
);

INSERT INTO polizamovtos 
  (`idlote`, `cargo`, `abono`, `fechamovto`)
VALUES
  ('2', '0', '5000', '2019-11-01'),
  ('1', '1000', '0', '2019-12-01'),
  ('2', '4000', '0', '2019-11-01'),
  ('1', '2000', '0', '2020-01-01'),
  ('2', '0', '500', '2020-10-02'),
  ('3', '4000', '0', '2020-01-01'),
  ('4', '5000', '0', '2020-01-01'),
  ('1', '2000', '0', '2020-01-01'),
  ('2',  '0',   '2000', '2020-04-02');

Query #1

SELECT 
IDLOTE,
SUM(cargo) as saldo 
FROM polizamovtos 
WHERE FECHAMOVTO='2020-01-01' 
GROUP BY IDLOTE
having sum(cargo)-sum(abono) > 0;

| IDLOTE | saldo |
| ------ | ----- |
| 1      | 4000  |
| 3      | 4000  |
| 4      | 5000  |

View on DB Fiddle

Query #2

SELECT 
IDLOTE,
SUM(CASE WHEN FECHAMOVTO='2020-01-01' 
    THEN cargo
   ELSE 0
   END) as saldo 
FROM polizamovtos 
GROUP BY IDLOTE
having sum(cargo)-sum(abono) > 0;

| IDLOTE | saldo |
| ------ | ----- |
| 1      | 4000  |
| 3      | 4000  |
| 4      | 5000  |

View on DB Fiddle