Invalid use of group function to get sum of field from two different tables-CodePudding

MySql Version 5.7.34

This query is working and showing the expected result fine

SELECT
    MAX(`a`.`Start_Balance_Qty`) - SUM(IFNULL(`b`.`QTY`, 0)) as remaining
FROM
    (
        `items` `a`
        LEFT JOIN `details` `b` ON
            (
                (
                    (`a`.`item_numer` = `b`.`PARTNO`)
                    AND
                    (`b`.`deliverystatusid` = '6')
                )
            )
    )
GROUP BY
    `a`.`item_numer`

The result is displaying one column which displaying the remaining quantity on the inventory tables as below :

    | remaining |
    |    50     |
    |    60     |
    |    10     |

I want to have the total sum of this column to display the result as below

    | TotalSum  |
    |    120    |

I tried to add sum but it gives me error invalid use of group function. I wrote the sum as below

SELECT
    SUM(
        MAX(`a`.`Start_Balance_Qty`) - SUM( IFNULL(`b`.`QTY`, 0) )
    ) as remaining
FROM
    (
        `items` `a`
        LEFT JOIN `details` `b` ON
        (
            (
                (`a`.`item_numer` = `b`.`PARTNO`)
                AND
                (`b`.`deliverystatusid` = '6')
            )
        )
    )
GROUP BY
    `a`.`item_numer`

what's the problem here ?

CodePudding user response：

SELECT sum(remaining) from (SELECT
    MAX(`a`.`Start_Balance_Qty`) - SUM(IFNULL(`b`.`QTY`, 0)) as remaining
FROM
    (
        `items` `a`
        LEFT JOIN `details` `b` ON
            (
                (
                    (`a`.`item_numer` = `b`.`PARTNO`)
                    AND
                    (`b`.`deliverystatusid` = '6')
                )
            )
    )
GROUP BY
    `a`.`item_numer` ) A

This should work. Use the inner results as another table and run another query above that table.

CodePudding user response：

While your original query works, it already looks a little weird, because of MAX(a.Start_Balance_Qty), which is essentially just a.Start_Balance_Qty, as you are selecting items and every item has one row in the items table containing this quantity. (The query would be more readable by the way, did you use mnemonic alias names, such as i for items and d for details.)

Now you want the sum of all item quantities, but consider each item only once. The best way to achive this is to aggreagte before joining.

Your orginal query modified:

select
   i.item_number,
   i.start_balance_qty - coalesce(d.sum_qty, 0) as remaining
from items i
left join
(
  select partno, sum(qty) as sum_qty
  from details
  where deliverystatusid = 6
  group by partno
) d on d.partno = i.item_number
order by i.item_number;

The new query:

select
   i.sum_qty - d.sum_qty as remaining
from
(
  select sum(start_balance_qty) as sum_qty
  from items
) i
cross join
(
  select sum(qty) as sum_qty
  from details
  where deliverystatusid = 6
) d
order by i.item_number;

(Or just use your original query as a base and add up the rows as shown in Muhammad Atif Akram's answer.)