You Asked
Hi,
We want to fetch the latest total value for emp id = 2 but its summing total_value for all the rows for emp_id = 2.
we want the result(total_value) only for the latest row of emp_id = 2. the statements and query are shown as below,
create table emp_test (emp_id number, emp_name varchar2(50),dept_id number, salary number)
/
create table dep_test (dept_id number, dept_name varchar2(50),resale number, total_cost number)
/
insert into emp_test values (1,'A',10,1000)
/
insert into emp_test values (2,'B',10,2000)
/
insert into emp_test values (3,'C',20,3000)
/
insert into emp_test values (4,'D',20,4000)
/
insert into emp_test values (5,'E',30,5000)
/
insert into dep_test values (10,'D10',1000,1000)
/
insert into dep_test values (10,'D20',2000,2000)
/
insert into dep_test values (20,'D30',3000,3000)
/
insert into dep_test values (20,'D40',4000,4000)
/
insert into dep_test values (10,'D50',4000,4000)
/
commit
/
Query:
select round(sum(a.total_value),2) total_value from
(select et.dept_id,(dt.toal_cost + dt.resale) total_value, max(et.emp_id) from emp_test et, dep_test dt
where et.dept_id = dt.dept_id
and et.dept_id = 10
group by et.dept_id,dt.toal_cost,dt.resale)a;
Please suggest.
Thanks.
and Connor said...
Thanks for the test case but I'm not really sure what you mean.
SQL> create table emp_test (emp_id number, emp_name varchar2(50),dept_id number, salary number)
2 /
Table created.
SQL>
SQL> create table dep_test (dept_id number, dept_name varchar2(50),resale number, total_cost number)
2 /
Table created.
SQL>
SQL>
SQL>
SQL> insert into emp_test values (1,'A',10,1000)
2 /
1 row created.
SQL> insert into emp_test values (2,'B',10,2000)
2 /
1 row created.
SQL> insert into emp_test values (3,'C',20,3000)
2 /
1 row created.
SQL> insert into emp_test values (4,'D',20,4000)
2 /
1 row created.
SQL> insert into emp_test values (5,'E',30,5000)
2 /
1 row created.
SQL> insert into dep_test values (10,'D10',1000,1000)
2 /
1 row created.
SQL> insert into dep_test values (10,'D20',2000,2000)
2 /
1 row created.
SQL> insert into dep_test values (20,'D30',3000,3000)
2 /
1 row created.
SQL> insert into dep_test values (20,'D40',4000,4000)
2 /
1 row created.
SQL> insert into dep_test values (10,'D50',4000,4000)
2 /
1 row created.
SQL>
SQL> commit
2 /
Commit complete.
SQL>
SQL>
SQL>
SQL> select *
2 from emp_test et, dep_test dt
3 where et.dept_id = dt.dept_id
4 and et.emp_id = 2;
EMP_ID EMP_NAME DEPT_ID SALARY DEPT_ID DEPT_NAME RESALE TOTAL_COST
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
2 B 10 2000 10 D10 1000 1000
2 B 10 2000 10 D20 2000 2000
2 B 10 2000 10 D50 4000 4000
3 rows selected.
SQL>
SQL>
SQL>
So that's the DEPT rows that equate to EMP_ID = 2... So what is it precisely you want from that ? We need more information
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment