query problem

Hi,
we have a problem with query and data can be reproduced
as :
create table dd1(dd int,dd1 int)
create table dd(dd int,dd1 int)
insert into dd values(1,10)
insert into dd values(2,20)
insert into dd1 values(2,20)
insert into dd1 values(3,20)
THIS STATEMENT WORKS
--
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd
union all
select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd
THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
----
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
)
group by dd
How to rewrite this query?
Thanks
--HarvinderHarvinder,
select dd,sum(dd1),sum(dd2)
from
((
select dd,sum(dd1) dd1,count(*) dd2 from dd
group by dd)
union all
(select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
group by dd)
) a
group by dd
the derived table needs an alias.
Quentin
"harvinder" <hs@.metratech.com> wrote in message
news:02b301c33f3b$1a33ab40$a301280a@.phx.gbl...
> Hi,
> we have a problem with query and data can be reproduced
> as :
> create table dd1(dd int,dd1 int)
> create table dd(dd int,dd1 int)
> insert into dd values(1,10)
> insert into dd values(2,20)
> insert into dd1 values(2,20)
> insert into dd1 values(3,20)
> THIS STATEMENT WORKS
> --
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd
> union all
> select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd
> THIS STATEMENT IS GIVING ERROR AT GROUP BY CLAUSE
> ----
> select dd,sum(dd1),sum(dd2)
> from
> ((
> select dd,sum(dd1) dd1,count(*) dd2 from dd
> group by dd)
> union all
> (select dd,-sum(dd1) dd1,-count(*) dd2 from dd1
> group by dd)
> )
> group by dd
> How to rewrite this query?
> Thanks
> --Harvinder
>sql