CODEWITHSUNDEEP
mysqlsubquerysubtraction
JNewbie
JNewbie

Reputation: 79

How to do a subtract of 2 columns that were the results of a sub-query

I have this query and it is working fine as expected, ie, it shows all the columns as well as the 2 columns from each sub-query...

SELECT 
    a.group_id,
    a.code,
    a.description_en,
    c.size_code,
    (
        SELECT
            SUM(b.qty)
        FROM
            receiving_details AS b
        WHERE
            b.code = c.size_code
    ) AS in_qty,
    (
        SELECT
            SUM(d.qty)
        FROM
            requisition_details AS d
        WHERE
            d. matl_code = c.size_code
    ) AS out_qty
FROM products AS a
INNER JOIN products_sizes AS c ON c.prod_code = a.code
ORDER BY a.group_id ASC, a.code ASC, c.size_code ASC

However, when I try to add this line...

(in_qty - out_qty) AS balance,

just before the 'FROM' statement, I get an error of Unknown column 'in_qty' in 'field list'.

What am I doing wrong?

EDIT:

From the accepted answer, I did a few more fix and got the result I wanted.

SELECT *, (e.in_qty - e.out_qty) AS balance FROM
(SELECT 
    a.group_id,
    a.code,
    a.description_en,
    c.size_code,
    (
        SELECT
            IFNULL(SUM(b.qty),0)
        FROM
            receiving_details AS b
        WHERE
            b.code = c.size_code
    ) AS in_qty,
    (
        SELECT
            IFNULL(SUM(d.qty),0)
        FROM
            requisition_details AS d
        WHERE
            d. matl_code = c.size_code
    ) AS out_qty
FROM products AS a
INNER JOIN products_sizes AS c ON c.prod_code = a.code) AS e
ORDER BY e.group_id ASC, e.code ASC, e.size_code ASC

Upvotes: 1

Views: 2015

Answers (1)

sgeddes
sgeddes

Reputation: 62841

You can't reference the same field from your select statement within itself. One option would be to move the results in another subquery, and then perform the calculation:

select *, (in_qty - out_qty) AS balance
from (
    SELECT 
        a.group_id,
        a.code,
        a.description_en,
        c.size_code,
        (
            SELECT
                SUM(b.qty)
            FROM
                receiving_details AS b
            WHERE
                b.code = c.size_code
        ) AS in_qty,
        (
            SELECT
                SUM(d.qty)
            FROM
                requisition_details AS d
            WHERE
                d. matl_code = c.size_code
        ) AS out_qty
    FROM products AS a
    INNER JOIN products_sizes AS c ON c.prod_code = a.code
) t
ORDER BY group_id, code, size_code

Upvotes: 1

Related Questions