M.Cooper M.Cooper - 4 months ago 14
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

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;
Comments