question:- I want to calculate the total rows of two different table and then calculate the difference of two rows between these tables:---
I have written two queries
select
(select count(*) from batchhr.tr_time_mar_intrfce_src ) cnt,
(select count(*) from batchhr.tr_time_mar_intrfce_tmp ) empl
from batchhr.tr_time_mar_intrfce_src, batchhr.tr_time_mar_intrfce_tmp;
select count(*) cnt from batchhr.tr_time_mar_intrfce_src a
union all
select count(*) emp from batchhr.tr_time_mar_intrfce_tmp b;
the second query generating the output as below:-
cnt
7736
1942
and the excepted output was:
cnt emp
7736 1942
the first query generating the output was:
cnt emp
7736 1942
7736 1942
7736 1942
and the excepted output is:
cnt emp
7736 1942
please help me
Your first query doesn't make any sense. It gives you the cartesian product of the two tables. So every row joined to every other row. Then returns the count for each table for every column.
To return the counts "side-by-side" as separate columns, you could:
- Return these as scalar subqueries against dual:
select (select count(*) c from hr.departments) dept,
(select count(*) c from hr.employees) emps
from dual;
DEPT EMPS
27 107
- Union all the counts as in your second query. Then pivot the results to get the values as columns:
with counts as (
select 'D' tab, count(*) c from hr.departments
union all
select 'E' tab, count(*) c from hr.employees
)
select * from counts
pivot (min(c) for tab in ('D' as dept, 'E' as emps));
DEPT EMPS
27 107
If you want to know more about pivot, check out this blog post:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to