Skip to Main Content
  • Questions
  • Count rows from two different tables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 03, 2016 - 9:44 am UTC

Last updated: May 25, 2022 - 3:46 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 24, 2022 - 3:50 pm UTC

select (select count(*) c from hr.departments) dept,
       (select count(*) c from hr.employees) emps 
from dual;

DEPT  EMPS  
27    107 


Exactly what I have just been looking for. Thanks
Connor McDonald
May 25, 2022 - 3:46 am UTC

glad we could help