Mysql select sum without VAT and VAT sum, where invoice total is at least-CodePudding

Have mysql table like this:

| DocumentNumber | Amount | Account | BuyerName | BuyerVat |
| 123            | 100    | 6110    | Name 01   | null     |
| 123            | 21     | 5727    | Name 01   | null     |
| 456            | 100    | 6112    | Name 02   | null     |
| 456            | 12     | 5732    | Name 02   | null     |
| 789            | 10     | 6112    | Name 02   | null     |
| 789            | 1.2    | 5732    | Name 02   | null     |

Want to get SUM(Amount) AS TotalWithoutVat where document total for Account 6110 or 6112 is at least 100 and BuyerVat is null, empty. And also want to SUM(Amount) AS TotalVat.

No big problems to get TotalWithoutVat.

SELECT SUM(`Amount`) AS `TotalWithoutVat` FROM `Table`
WHERE 
`Account` IN("6110", "6112") 
AND `BuyerVat` IS NULL 
HAVING SUM(`Amount`) >= "100"

But how to get total of VAT amount for those documents, which total without VAT is at least 100 and BuyerVat is null? Can not use HAVING SUM(Amount) >= x because x may be 21 or 12 or 5 (different VAT rates).

At the moment resources very consuming solution. At first from mysql get all invoice numbers where total without VAT is at least 100 and BuyerVat is null. Then select sum amount where invoice numbers are in those selected.

Select all invoice numbers.

SELECT SUM(`Amount`) AS `TotalWithoutVat`, `DocumentNumber` FROM `Table`
WHERE 
`Account` IN("6110", "6112") 
AND `BuyerVat` IS NULL 
GROUP BY `JL`.`DocumentNumber` 
HAVING SUM(`Amount`) >= "100"

Then php foreach existing array and create new array with document numbers $document_numbers[] = trim($one_line['DocumentNumber']);

Then next mysql

SELECT SUM(`Amount`) AS `TotalVat` FROM `Table`
WHERE 
`DocumentNumber` IN("123", "456") -- `$document_numbers` 
AND `Account` IN ("5727", "5732")

Any idea how to get the same with one mysql query?

Expecting such output:

[0] => Array
    (
        [TotalWithoutVat] => 200.00
        [TotalVat] => 33.00

Based on nnichols idea, created such code (at the moment seems works correctly, but may be some unexpected results; need to test). As i understand, from the same table i create as if two "virtual" tables with necessary columns and rows. Then "inside mysql" do calculations (sum values) and only then get out results from mysql? Seems good solution and much less resource usage.

SELECT `t1`.`TotalWithoutVat`, `t2`.`TotalVat` 

FROM (
SELECT SUM(`Amount`) AS `TotalWithoutVat`, 
`DocumentNumber` -- as i understand this is necessary for joining; without get `Unknown column` error 
FROM `Table`
WHERE `Account` IN (6110, 6112) 
AND `BuyerVat` IS NULL 
-- GROUP BY `DocumentNumber` -- in this case do not need, because i need only one total sum
HAVING SUM(`Amount`) >= 100
) `t1`

INNER JOIN 
( -- otherwise i got incorrect totals...
SELECT SUM(`Amount`) AS `TotalVat`, `DocumentNumber` 
FROM `Table`
WHERE `Account` IN (5727, 5732) 
AND `BuyerVat` IS NULL 
) `t2`

ON `t1`.`DocumentNumber` = `t2`.`DocumentNumber`;

CodePudding user response：

I am not sure that I fully understand what you are trying to do but based on the two SELECT queries you have my best guess is -

SELECT `t1`.*, SUM(`t2`.`Amount`) AS `TotalVat`
FROM (
    SELECT SUM(`Amount`) AS `TotalWithoutVat`, `DocumentNumber`
    FROM `Table`
    WHERE `Account` IN (6110, 6112) 
    AND `BuyerVat` IS NULL 
    GROUP BY `DocumentNumber` 
    HAVING SUM(`Amount`) >= 100
) `t1`
INNER JOIN `Table` `t2`
    ON `t1`.`DocumentNumber` = `t2`.`DocumentNumber`
    AND `t2`.`Account` IN (5727, 5732)
GROUP BY `t1`.`DocumentNumber`;

Or possibly without the grouping by DocumentNumber on the outer query?

SELECT SUM(`t1`.`TotalWithoutVat`) `TotalWithoutVat`, SUM(`t2`.`Amount`) AS `TotalVat`
FROM (
    SELECT SUM(`Amount`) AS `TotalWithoutVat`, `DocumentNumber`
    FROM `Table`
    WHERE `Account` IN (6110, 6112) 
    AND `BuyerVat` IS NULL 
    GROUP BY `DocumentNumber` 
    HAVING SUM(`Amount`) >= 100
) `t1`
INNER JOIN `Table` `t2`
    ON `t1`.`DocumentNumber` = `t2`.`DocumentNumber`
    AND `t2`.`Account` IN (5727, 5732);