Adding 2 results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding 2 results

Author

Topic

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2007-11-28 : 07:23:06

How can i add the result of 2 select statement with out join
ie
i have 2 queries
select count(*) from product
select count(*) from category

i want the sum of this 2 result to be returned

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 07:27:43

[code]select sum(cnt)
from
(
select cnt = count(*) from product
union all
select cnt = count(*) from category
) a [/code]

KH
[spoiler]Time is always against us[/spoiler]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 07:28:16

[code]
select count(1) + (select count(1) from category) as CountOfProdAndCat from product
[/code]
Why would you want to do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 07:28:16

[code]select (select count(*) from product) +
(select count(*) from category)[/code]

KH
[spoiler]Time is always against us[/spoiler]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 07:33:47

3 answers, 3 different ways.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-28 : 16:43:01

Or a 4th option...
[CODE]
DECLARE @x int, @y int

SET @x = (SELECT Count(*) FROM product)
SET @y = (SELECT Count(*) FROM category)

SELET @x + @y As [Oh look!]
[/CODE]

George
<3Engaged!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-29 : 09:23:32

5th

SELECT p.cnt + c.cnt
FROM
      (SELECT cnt = COUNT(*) FROM product) p 
CROSS JOIN
      (SELECT cnt = COUNT(*) FROM category) c

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources