making a select sum on a union select

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 2000 Forums

SQL Server Development (2000)

making a select sum on a union select

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-20 : 07:19:57

i make a union query :


select sum(*),reason from (
select count(*),reason from table1 group by reason
union all
select count(*),reason from table2 group by reason
)
group by reason

the outer SUM(*) dosent work -why?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 07:21:18

[code]select sum(cnt),reason from (
select count(*) as cnt,reason from table1 group by reason
union all
select count(*),reason from table2 group by reason
) Temp
group by reason[/code]

Edit: added table alias for derived table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jrogers
Starting Member

34 Posts

Posted - 2008-03-20 : 07:22:52

[code]select sum([count]),reason from (
select count(*) [count],reason from table1 group by reason
union all
select count(*),reason from table2 group by reason
) test
group by reason[/code]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-20 : 07:39:24

i did like this :


 SELECT sum(cnt),reason
from
(
SELECT     COUNT(*) AS cnt, Reason
FROM         Tbl_reason2007
WHERE     (Company = 'latest')
  group by reason
UNION ALL
SELECT     COUNT(*), reason
FROM         Tbl_reason2008
WHERE     company = 'latest'
  group by reason

)
 group by reason

and i get :

quote:
Incorrect syntax near the keyword 'group'.

why - i did the same as the xample you gave me?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 07:42:21

look at jrogers example... (harsh did a typo i think

)

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 07:43:53

See my edited post.

Elan,
Yes it was typo

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-20 : 07:51:19

first thanks all of u!
about the Temp table -this meaans a table called "temp" is created in the fly or what?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 07:54:18

it doesn't really exist, it's just a derived table for use in your query

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 07:54:47

It is not actual table, it is resultset which is referred to by given alias.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-03-20 : 09:34:31

ok thnaks all

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)