-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

New contributor
Sergio Rossetti is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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

| improve this answer | |
  • 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 yesterday
  • @SergioRossetti If I want to add another column next the same logic I cannot understand what you mean. – Akina yesterday
  • i got it sorry, thanks man. – Sergio Rossetti yesterday
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

| improve this answer | |
  • 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 yesterday
  • I need WHERE FECHAMOVTO='2020-01-01' only for the second column "saldo" – Sergio Rossetti yesterday
  • 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 yesterday
  • 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 yesterday
  • Yes i finished the report adding cases thanks a lot.. – Sergio Rossetti yesterday

Your Answer

Sergio Rossetti is a new contributor. Be nice, and check out our Code of Conduct.

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.