CODEWITHSUNDEEP
sql-server
Revokez
Revokez

Reputation: 323

SQL Creating a view with an additional sum column

Hi I'm trying to create a view from two different tables that has an additional column that is a sum of Table1.Price1 - Table2.Price2. The view without the additional column is:

Create View testview AS (
SELECT t1.ID,t1.Price1,t2.Price2 FROM
Table1 t1
LEFT JOIN
Table2 t2
ON
t1.ID = t2.ID
);

Any help would be much appreciated, thank you.

Below is a representation of what the view would look like:

ID    | Table1.Price1 | Table2.Price2 | Total | 
---------------------------------------   
1 | 15.00       | 5.00  | 10.00    |

2 | 10.00       | 2.50  | 7.50     |

Upvotes: 0

Views: 69

Answers (3)

Ritikesh
Ritikesh

Reputation: 1228

Try this:

Create View testview AS 
(SELECT t1.ID,t1.Price1,t2.Price2, (t1.Price1 - t2.Price2) as difference
FROM Table1 t1 
LEFT JOIN Table2 t2 
ON t1.ID = t2.ID );

Upvotes: 2

overflowed
overflowed

Reputation: 66

You can just add two columns with the +,-,*,/ operators like in:

Create View testview AS (
   SELECT t1.ID,t1.Price1,t2.Price2, t1.Price1+t2.Price2 as total FROM
   Table1 t1
   LEFT JOIN
   Table2 t2 ON t1.ID = t2.ID
);

This will result in your desired table structure

Upvotes: 0

rory.ap
rory.ap

Reputation: 35270

Just add your SUM column, like this: 

Create View testview AS (
SELECT t1.ID,t1.Price1,t2.Price2, t1.Price1-t2.Price2 AS [SUM] FROM
Table1 t1
LEFT JOIN
Table2 t2
ON
t1.ID = t2.ID
);

Upvotes: 0

Related Questions