M.Cooper M.Cooper - 1 year ago 62
SQL Question

Subtract two numeric column values in Oracle query

I need to subtract two column values and display the output column in select query.

SELECT c.name,
(SELECT SUM(p.qty*s.price)
FROM stock s, purchase p
WHERE s.company = p.company
GROUP BY p.company)
MINUS
(SELECT SUM(p.price*p.qty)
FROM stock s, purchase p
WHERE s.company = p.company
GROUP BY p.company) AS "Profit/Lost"
FROM purchase p, client c, stock s
WHERE s.company = p.company
AND c.clno = p.clno
GROUP BY p.company;


But it will display an error. How to solve?

Answer Source

First subquery needs an Alias.

but why use subquery at all? Is this what you want?

SELECT c.name, p.company,
   SUM(p.qty*s.price) - SUM(p.price*p.qty) roe
FROM client c
   join purchase p on p.clno  = c.clno 
   join stock s on s.company = p.company 
GROUP BY c.name, p.company;