Adding values of select count(*)

[roche786]

Hi,

I wanted to know , how to add values of two select count(*)'s in one query

for ex.

Query 1)select count(*) from a

quesry 2) select count(*) from b

Restlt : Select Sum of the values of both query 1 and query 2

Thanks

 

[lewisp]

SELECT (select count(*) from a) +
       (select count(*) from b)
FROM sys.dual;

 

[roche786]

Hi,

That didn't work. It didnt give me any output when i tried that . Is there another way , please let me know.

I tried this

select sum(count_total) from (
select count(*) count_total from a
union
select count(*) count_total from b)

ofcourse it only works if the values from each quesry is different , if they are same for eg: 1 for query a and 1 for query b then the result should be 2 as i want ot but the output is 1.

Thanks

 

[SantaMufasa]

That didn't work. It didnt give me any output when i tried that.

Lewis's code is absolutely solid. If you didn't receive any output, then something else is wrong. Please post a copy-and-paste of your code, its execution, and the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[slaing]

I agree with the previous posts - summing the 2 count()s in a select from dual should work . I can't see how you could get no output at all (I presume you're getting output from other SQL statements. Let us know if you work that one out !

Anyway, if you want to stick with your code that almost works, try using UNION ALL instead of UNION - that prevents Oracle from removing similar results.

Steve

 

[roche786]

Thanks to all of you for the help.

It works great now!

 

[jbpelletier]

just to know..

should not "union" be replace by "union all"?
if i remmeber right union will act as a "select disticnt" so if both value are equal.. like 5 and 5 the result would be 5 instead of 10?

maybe im wrong...

jb

 

[SantaMufasa]

JB,

Yes, As Slaing mentioned on 12 Aug, UNION ALL is the appropriate option in this case since we do not want Oracle eliminating duplicate COUNT(*) values.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.