SUM() in a clause Where - Mysql

Asked

Viewed 73 times

1

The query below brings results of a contract, whose contract delay is between 1 and 487 days.

Now I want somar the values of faturas pertencentes to the contract invoice.value and then filtrar for saldo of contrato, whose total is between one value and another.

But I’m not getting it.

In this case I would have 8 results that would fit the consultation below, with the balance of the contract between 4 e 5 reais and atraso amid 1 e 487 dias, but I only get paid 1 resultado.

What am I doing wrong ?

SELECT DISTINCT `contract`.*,
SUM(invoice.value) as amount
FROM`tb_contract` `contract`
LEFT JOIN `tb_invoice` `invoice` ON `invoice`.`contract_id` = `contract`.`id`
WHERE `contract`.`creditor_id` = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'
AND DATEDIFF(CURDATE(), invoice.due_date) >= 1
AND DATEDIFF(CURDATE(), invoice.due_date) <= 487
GROUP BY `contract`.`contract`
HAVING `amount` between 4 AND 5
mysql

  • Looks like you need some adjustment on WHERE and/or GROUP BY / HAVING

  • amount is integer ? " > 1" is not "catching" with 1 day.

  • amount is decimal, the delay is working well, the question here is only about the balance (amount)

  • took out the having to see the result ?

  • I’ve tried without Having, using invoice.value, but I couldn’t. Actually when I use invoice.value me returns a contract that has at least 1 invoice that fits between the values, however brings other invoices of the contract who contains several other values above the parameters informed. What I want is to sum all the contract invoices and filter only the sums that are between one value and another. That is, all contract invoices cannot be lower or higher than the value parameters.

  • Note that HAVING is different from WHERE: WHERE filters the lines individually before the GROUP BY application while HAVING filters the group of lines created by GROUP BY.

  • tried HAVING SUM(Invoice.value) between 4 and 5 or HAVING SUM(Invoice.value) between 4.00 and 5.99 ? because 1 'amount' can be a column 2 the value is decimal // I said take the having to see what is behind and analyze the result // tb see SELECT contract.contract, SUM(Invoice.value) as amount // distinct and group by together .... they brigarsrs

Show 2 more comments

1 answer

0

Use the sub-select is the best option for this type of problem:

SELECT res.* 
FROM (
      SELECT DISTINCT `contract`.*,
      SUM(invoice.value) as amount
      FROM`tb_contract` `contract`
      LEFT JOIN `tb_invoice` `invoice` ON `invoice`.`contract_id` = `contract`.`id`
      WHERE `contract`.`creditor_id` = '5ddf5246-fed4-4e5f-538d-34df1e8cf9ee'
      AND DATEDIFF(CURDATE(), invoice.due_date) >= 1
      AND DATEDIFF(CURDATE(), invoice.due_date) <= 487
      GROUP BY `contract`.`contract`
      ) res
WHERE res.`amount` between 4 AND 5

First you perform the sums and groupings in the sub-select and then performs the filter.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.