question

dlmagers avatar image
dlmagers asked

Create a View named ORDER_TOTAL

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. Can someone point me in the right direction?
homeworkview
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered
This is off the cuff and untested, but the view should look vaguely like: create view vw_order_total as select o.Order_num, sum(ol.quoted_price) as Total_amount from orders o join order_lin ol on o.order_num = ol.order_num group by o.Order_num And then as Matt very astutely pointed out, you cannot have an order by inside a view, and generally wouldn't want to anyway. So, you get the order by from querying the view: select Order_num, Total_amount from vw_order_total order by o.order_num That is assuming that the quoted_price in order_line is the entire price for that line. The third table is simply unneeded as long as quoted_price is indeed the entire price for that line item. [Edited to fix the order by inside a view. Thanks again, Matt.]
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, although you need to specify TOP 100 PERCENT to order by in a view - which also makes me wonder why the ordering would be required in a view... almost.
3 Likes 3 ·
@Matt Whitfield Someone who does not like the cheesy **top 100 percent** can, for the modest price of just 4 bytes per record, opt to include something like a **row_number() over (order by o.Order_num) N** as the first column in the select, which allows getting rid of both the order by clause and the top 100 percent :)
2 Likes 2 ·
Good catch, Matt. I actually wrote the query first, then noticed the question talked about it being in a view so I went back and added the create view without considering the order by. Editing now to account for that.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.