CODEWITHSUNDEEP
sql-servert-sql
Rod
Rod

Reputation: 15475

Subtract total column in table2 from total column in table1

Table1

Color, Total
------------
Red,5
Blue,3
Pink,1

Table2

Color, Total
------------
Red,3
Blue,2
Pink,1

The desired result would be:

Results
Color, Total
------------
Red,2
Blue,1
Pink,0

How do I subtract total column in table2 from total column in table1?

http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/2498

Upvotes: 0

Views: 52

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175974

JOIN is good option, but let's think different:

SqlFiddle

SELECT t.color, SUM(t.total) as total
FROM (
  select color, total  from @Table1
  UNION ALL
  select color, -total  from @Table2) AS t
GROUP BY t.color
ORDER BY total DESC

Upvotes: 2

Ullas
Ullas

Reputation: 11556

Use JOIN

DECLARE @Table1 Table (color nvarchar(4), total int)
INSERT INTO @Table1  Values ('Red', 5)
INSERT INTO @Table1  Values ('Blue', 2)
INSERT INTO @Table1  Values ('Pink', 1)

DECLARE @Table2 Table (color nvarchar(4), total int)
INSERT INTO @Table2  Values ('Red', 3)
INSERT INTO @Table2  Values ('Blue', 1)
INSERT INTO @Table2  Values ('Pink', 1)

SELECT t1.color,t1.total - t2.total as total
from @Table1 t1
join @Table2 t2
on t1.color = t2.color;

SQL Fiddle

Upvotes: 3

dotnetom
dotnetom

Reputation: 24916

You need to join tables and subtract values:

SELECT t1.color, t1.total - t2.total as Total
FROM @Table1 t1 INNER JOIN @Table2 t2 
    ON t1.color = t2.color

Here is updated SQL Fiddle

Upvotes: 4

Related Questions