-1

Hi i need information about how to merge both sentences

Sentence 1

select idlote from polizamovtos group by idlote having sum(cargo)-sum(abono) > 0

Sentence 2

SELECT SUM(cargo) as saldo FROM polizamovtos WHERE FECHAMOVTO='2020-01-01' GROUP BY IDLOTE

sentence 1 is the main filter

Hi, I attach more information

table polizamovtos enter image description here

Output expected

idlote cargo
1 4000
3 4000
4 5000

0

Sergio Rossetti => I need WHERE FECHAMOVTO='2020-01-01' only for the second column saldo

Test

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);

fiddle

  • It is exactly what I was looking for. Thanks, If I want to add another column next the same logic, how it would be? for example a third column – Sergio Rossetti Aug 1 '20 at 17:43
  • @SergioRossetti If I want to add another column next the same logic I cannot understand what you mean. – Akina Aug 1 '20 at 17:45
  • i got it sorry, thanks man. – Sergio Rossetti Aug 1 '20 at 17:50
0

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

  • Hi,the problem is that the polizamovtos table has other records that do not have the first criteria: sum (cargo) -sum (abono)> 0; – Sergio Rossetti Aug 1 '20 at 17:23
  • I need WHERE FECHAMOVTO='2020-01-01' only for the second column "saldo" – Sergio Rossetti Aug 1 '20 at 17:28
  • 1
    @SergioRossetti The output matches to one you post. If there exists some special situation when the provided solution gives wrong output then edit the fiddle and show where the output is wrong and what is the logic of difference. – Akina Aug 1 '20 at 17:33
  • as Akina said, you can move the where clause to the sum see updated answer, if you have still problems please add your own data, so that o can chekc it – nbk Aug 1 '20 at 17:44
  • Yes i finished the report adding cases thanks a lot.. – Sergio Rossetti Aug 1 '20 at 19:09

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.