We help IT Professionals succeed at work.

sql query

Sarma Vadlamani
on
Hi,
I need  a query to total the followng

select count(*) from emp
union
select count((*) from dept

Result
10
5

Expected is the total of both rows that is 15.
can youplease advise. thanks
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
select sum(mycount) from
(
select count(*) mycount from emp
 union
 select count(*) from dept
);
johnsoneSenior Oracle DBA

Commented:
I would recommend using UNION ALL instead of UNION.  Gets rid of an unnecessary sort and if the 2 counts are the same, you may get unexpected results.
johnsoneSenior Oracle DBA

Commented:
I take back my "may get unexpected results" and replace it with "will get unexpected results".

Test case:
create table emp (id number);
create table dept (id number);
insert into emp values (1);
insert into emp values (2);
insert into emp values (3);
insert into dept values (1);
insert into dept values (2);
insert into dept values (3);
commit;
select sum(mycount) from
(
select count(*) mycount from emp
 union
 select count(*) from dept
);

Open in new window

Result is a count of 3, not 6.  UNION dupes out the second count because they are the same.
Naitik GamitSoftware Developer
Top Expert 2015

Commented:
you can directly use as,

SELECT  (SELECT COUNT(*) FROM emp) + (SELECT COUNT(*) FROM dept) AS Total_Sum
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
One correction.  In Oracle you need to select FROM something.
SELECT  (SELECT COUNT(*) FROM emp) + (SELECT COUNT(*) FROM dept) AS Total_Sum FROM DUAL;

That said:  Yes you 'can' do it this way but I wouldn't recommend it.