need to write query...

need to write query...
table1
fname points
-- --
bart -10
homer 0
lisa 20
maggy 5
marge 13
table2
fname points
-- --
bart 5
homer 7
lisa 8
marge -5
need to display report of names ordered by total score descending only
if total is above 0 so result should look like...
fname points
-- --
lisa 28
marge 8
homer 7
maggy 5Try the following:
declare @.table1 table (fname varchar(20), points int)
declare @.table2 table (fname varchar(20), points int)
insert into @.table1
values ('bart',-10)
insert into @.table1
values ('homer',0)
insert into @.table1
values ('lisa',20)
insert into @.table1
values ('maggy',5)
insert into @.table1
values ('marge',13)
insert into @.table2
values ('bart',5)
insert into @.table2
values ('homer',7)
insert into @.table2
values ('lisa',8)
insert into @.table2
values ('marge',-5)
select fname
, SUM(points) points
from (
select t1.fname
, t1.points
from @.table1 t1
union all
select t2.fname
, t2.points
from @.table2 t2
) t
group by fname
having SUM(points) > 0
order by SUM(points) desc
"uspensky@.gmail.com" wrote:
> need to write query...
> table1
> fname points
> -- --
> bart -10
> homer 0
> lisa 20
> maggy 5
> marge 13
> table2
> fname points
> -- --
> bart 5
> homer 7
> lisa 8
> marge -5
> need to display report of names ordered by total score descending only
> if total is above 0 so result should look like...
> fname points
> -- --
> lisa 28
> marge 8
> homer 7
> maggy 5
>|||nevermind
got it
select fname as FirstName, Total=sum(points)
from (select fname, points from t1
UNION ALL
select fname, points from t2) as result
group by fname Having sum(points)>0 order by Total desc