Skip to Main Content
  • Questions

  • Count rows from two different tables

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.