Hi experts..
I want to display the list of first 3 highest ordered product and the total quantity.
My table consist of
Customer Id Order_Date Item Quantity Price
1 30-Jun-2013 Pogo stick 1 28
2 30-Jan-2013 Raft 1 58
2 2-Feb-2013 Tent 1 99
3 5-Dec-2013 Pen 2 5
4 15-Oct-2013 Tent 1 98
5 5-Jan-2013 Paper 10 15
6 9-Mar-2013 Mobile 1 1500
7 26-Apr-2013 Shirt 7 150
8 27-Apr-2013 Pant 1 450
9 18-Jun-2013 Shoe 5 690
10 18-Nov-2013 Tent 3 300
3 4-Jan-2013 Laptop 1 10000
4 14-Dec-2013 Ring 1 100
5 14-Dec-2013 Pen 3 60
6 21-Dec-2013 Shoe 1 500
I have tried this queries.. But i did not get exact output.
1) Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item having COUNT(quantity)>1 order by COUNT(quantity) desc;
2) Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc;
3) Select Item,COUNT(quantity) AS TOTAL_ORDER,SUM(price) AS Total_Price from Items_Order Group By item order by COUNT(quantity) desc;
4) select top 3 * FROM (Select Item AS ITEM,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item having COUNT(quantity)>1) AS ord;
5) select top 3 * FROM (Select Item AS ITEM,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc) AS ordered;
6) select top 3 * FROM (Select Item,COUNT(quantity) AS TOTAL_ORDER,SUM(price) AS Total_Price from Items_Order Group By item order by COUNT(quantity) desc) AS ordered;
1,2,3 and 4th is working. But I did not get the exact output.
5 and 6 is not working. It show some error.
The error message is:
Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
How to use sub query's to get the output. Please help me..