Oracle - Sum in select query

Riddhi Valecha

11y1.2kLearn iOS Programming

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 !

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir11y ago
Accepted 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 Valecha11y ago
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 Choudhary11y ago
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
Guest User11y ago
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.