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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
select sum(mycount) from
(
select count(*) mycount from emp
 union
 select count(*) from dept
);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.