Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
correlated subquery & top & view & derived table & group by
-- correlated subquery & top & view & derived table & group by ---Enjoy Coding by alex ogrinja /* Table of contents - create and insert into tables, - select *, - Return sellers who have in their portfolio transactions greater than or equal to the highest average of portfolios (in 3 steps). */ --->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> create table orders (ord_no int , purch_amt float , ord_date date , customer_id int , salesman_id int); insert into orders values (70001, 150.5 , '2012-10-05', 3005 , 5002), (70002 , 65.26 , '2012-10-05' , 3002 , 5001), (70004 , 110.5 , '2012-08-17' ,3009 , 5003), (70007 , 948.5 , '2012-09-10', 3005 , 5002), (70005 , 2400.6 , '2012-07-27', 3007 , 5001), (70008 , 5760 , '2012-09-10' , 3002 , 5001), (70010, 1983.43 , '2012-10-10', 3004 , 5006), (70003 , 2480.4 , '2012-10-10' , 3009 , 5003), (70012 , 250.45 , '2012-06-27' , 3008 , 5002), (70011 , 75.29 , '2012-08-17' , 3003 , 5007), (70013 , 3045.6, '2012-04-25' ,3002, 5001); --->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> go select * from orders o order by o.salesman_id, o.customer_id; go --->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Scenario: Return sellers who have in their portfolio transactions greater than or equal to the highest average of portfolios. -- Step 1: Using syntax "group by" return the max avg of the salesmen portofolios, and saved as view "avg_max_porto". create view avg_max_porto as select top 1 o.salesman_id, avg(o.purch_amt) avg_tranzac_porto from orders o group by o.salesman_id order by avg(o.purch_amt) desc; go -- Step 2: Cut off the view "avg_max_porto" at max avg portofolios 1 figure. select a.avg_tranzac_porto from avg_max_porto a go -- Step 3: Getting the expected result. select * from orders o1 where (select a.avg_tranzac_porto from avg_max_porto a) <= (select avg(o2.purch_amt) from orders o2 where o1.salesman_id= o2.salesman_id) order by o1.customer_id; ---Enjoy Coding by alex ogrinja
run
|
edit
|
history
|
help
0
jueves
ms sql update,insert
ankit
Except Insert missing
hdudh
BT SQL Project
range and pattern matching
SCHOOL-BUILD
SQL Stringdata
CTE reqursive query