CODEWITHSUNDEEP
mysqlsqldatabaseselect
Pradip Kharbuja
Pradip Kharbuja

Reputation: 3542

Using virtual column in mysql

I have mysql query like below :

SELECT
  company_name,
  SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
  total_sold_qty,
  (total_purchase_qty - total_sold_qty) AS rem_qty
FROM tbl_company
  JOIN tbl_share_purchase
ON purchase_company_id = tbl_company.id
  JOIN (SELECT
      sold_company_id,
      SUM(sold_qty)   AS total_sold_qty
    FROM tbl_share_sales
    GROUP BY sold_company_id) s
ON s.sold_company_id = tbl_company.id
GROUP BY purchase_company_id

Is it possible to use (total_purchase_qty - total_sold_qty) AS rem_qty to get remaining quantity?

Upvotes: 1

Views: 1460

Answers (1)

John Woo
John Woo

Reputation: 263803

you can do that if you wrap it on subquery, but you can't do the calculation using alias if its define on the same level,

SELECT  company_name,
        SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
        total_sold_qty,
        (SUM(purchase_qty * num_of_certificate) - total_sold_qty) AS rem_qty
FROM    tbl_company
        JOIN tbl_share_purchase
            ON purchase_company_id = tbl_company.id
        JOIN 
        (
            SELECT  sold_company_id,
                    SUM(sold_qty)   AS total_sold_qty
            FROM    tbl_share_sales
            GROUP   BY sold_company_id
        ) s ON s.sold_company_id = tbl_company.id
GROUP   BY purchase_company_id

here's using subquery,

SELECT  company_name,
        total_purchase_qty,
        total_sold_qty,
        (total_purchase_qty - total_sold_qty) AS rem_qty
FROM
(
    SELECT  company_name,
            SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
            total_sold_qty
    FROM    tbl_company
            JOIN tbl_share_purchase
                ON purchase_company_id = tbl_company.id
            JOIN 
            (
                SELECT  sold_company_id,
                        SUM(sold_qty)   AS total_sold_qty
                FROM    tbl_share_sales
                GROUP   BY sold_company_id
            ) s ON s.sold_company_id = tbl_company.id
    GROUP   BY purchase_company_id
)

Upvotes: 4

Related Questions