Create a View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Create a View

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-11-15 : 12:40:28

Ok, I have got a twister. Please help.

I need to create a view that consists of the order_num, and order total for each order currently on file. Sorting the rows by order_num. Using TOTAL_AMOUNT as the name for the order total.

Tables:

Orders: order_num, order_date, customer_num
Order_line: Order_num, Part_num, Num_ordered, quoted_price
Part: part_num, description, on_hand, class, warehouse, price

I missed this question on a test and I know I will see it again on my final. I want to know how to work it right.

Can someone point me in the right direction?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-15 : 23:07:11

If you need to use the quoted price to determine the total, then it's this:


CREATE VIEW yourView
AS
SELECT	o.order_num, SUM(l.quoted_price) as order_total
FROM	Orders as o
INNER JOIN
	Order_line as l
On	l.Order_num = o.order_num
GROUP BY
	o.order_num
ORDER BY
	o.order_num;

If you need to use the price then it's this:


CREATE VIEW yourView
AS
SELECT	o.order_num, SUM(p.price) as order_total
FROM	Orders as o
INNER JOIN
	Order_line as l
On	l.Order_num = o.order_num
INNER JOIN
	Part as p
On	p.part_num = l.Part_num
GROUP BY
	o.order_num
ORDER BY
	o.order_num;

That said, while it's valid to have an ORDER BY clause in a view, it is a good idea in most cases not to. The ORDER BY belongs in the SELECT statement that queries the view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources