Link to home
Create AccountLog in
Avatar of Sarma Vadlamani
Sarma Vadlamani

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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.
you can directly use as,

SELECT  (SELECT COUNT(*) FROM emp) + (SELECT COUNT(*) FROM dept) AS Total_Sum
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.