Oracle - Sum in select query

Question

Hi all...

I have a query with the following output -

select E.EmpCode,

(select count (D.id) from Dept D where D.Location = E.Location) as "A"),

(select count (D.id) from Add D where D.Location = E.Location) as "B"),

(select count (D.id) from Dept D where D.DeptName = E.DeptName) as "C")

from Employees E

EmpCode	A	B	C	D=A+B-C
100A	2	4	7	2+4-7 = -1
101A	3	5	7	3+5-7 = 1

How to add one more column in this query and get the result - Row-wise total in "D" Column ??

Please guide and Help !

Thank a ton !

Manoj Bhoir · Answer

Hi Try this query : Select * (x.A+x.B-x.C) as D From ( select E.EmpCode (select count (D.id) from Dept D where D.Location = E.Location) as A) (select count (D.id) from Add D where D.Location = E.Location) as B) (select count (D.id) from Dept D where D.DeptName = E.DeptName) as C) from Employees E) x

Riddhi Valecha · Answer

select E.EmpCode (select count (D.id) from Dept D where D.Location = E.Location) as A) (select count (D.id) from Add D where D.Location = E.Location) as B) (select count (D.id) from Dept D where D.DeptName = E.DeptName) as C) from Employees E Hi all... I am stuck up again here.... For Column-D (A+B-C = D) ; I want that if the user selects a number in D-Column in GridView ; then that many records must be displayed. i.e. I want a query - (select * from tab as A) + (select * from tab as B) - (select * from tab as C) How to implement this ?? PLease guide..

Pankaj Kumar Choudhary · Answer

Hello @Riddhi you can use this code: Select * (A+B-C) D From ( select E.EmpCode (select count (D.id) from Dept D where D.Location = E.Location) as A) (select count (D.id) from Add D where D.Location = E.Location) as B) (select count (D.id) from Dept D where D.DeptName = E.DeptName) as C) from Employees E)tab

pritaeas · Answer

I think the easiest way is: select * A + B - C AS D from ( select E.EmpCode (select count (D.id) from Dept D where D.Location = E.Location) as A) (select count (D.id) from Add D where D.Location = E.Location) as B) (select count (D.id) from Dept D where D.DeptName = E.DeptName) as C) from Employees E ) tmp I'm not familiar with Oracle so perhaps it's possible within your orignal query without the sub-select.