How do you subtract contents in one row from another row in the same table?

Question

I have query that gives me a table with several rows.

 

My dilemma is that I want to add a row (with union) at the bottom of the table that shows the difference between two rows in the table

 

Anybody have tips for me?

 

Answer 1

helghe:

Maybe something like this is what you are looking for?

declare @myTable table
   ( myId     integer,
        myValue  integer
   )

insert into @myTable values (1, 35)
insert into @myTable values (2, 14)

select convert (varchar (10), myId) as myId, myValue from @myTable where myId in (1,2)
union all
select 'Difference', a.myValue - b.myValue
  from @myTable a
 inner join @myTable b
    on a.myId = 1
   and b.myId = 2

-- ------------  Sample Output  ------------

--  myId       myValue    
--  ---------- -----------
--  1          35
--  2          14
--  Difference 21

Answer 2

Thanks Waldrop, that's gonna rid me of several headaches :)