sql query

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
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
select sum(mycount) from
(
select count(*) mycount from emp
 union
 select count(*) from dept
);
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
Naitik GamitSoftware DeveloperCommented:
you can directly use as,

SELECT  (SELECT COUNT(*) FROM emp) + (SELECT COUNT(*) FROM dept) AS Total_Sum
0
 
slightwv (䄆 Netminder) 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.