How to show the list of first 3 highest ordered product and the total quantity using SQL SERVER

Question

0.00/5 (No votes)

See more:

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..

Posted 11-Dec-13 6:31am

Vijaydhas

Add a Solution

Comments

Ashish_Agrawal 11-Dec-13 12:39pm

What do you mean by 'exact output'? Can you please post expected output of your table.

Vijaydhas 11-Dec-13 12:48pm

Query:
Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item having COUNT(quantity)>1 order by COUNT(quantity) desc;

O/P
Item Total_Order
Tent 3
Pen 2
Shoe 2

When i pass this query into sub query
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;

O/P
Item Total_Order
Pen 2
Shoe 2
Tent 3

Query:
Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc;

O/P

Item Total_Order
Tent 3
Shoe 2
Pen 2
Pogo stick 1
Raft 1
Ring 1
Shirt 1
Laptop 1
Mobile 1
Pant 1
Paper 1

Now i want pass this query to select top 3 rows. I pass like this:

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;

But it shows error:
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.

Ashish_Agrawal 11-Dec-13 12:56pm

Why are you using subquery when you can get the result through single query. But if you have to use sub query you can do like this..

SELECT Top 3 * FROM (SELECT item as ITEM, COUNT(quantity) as TOTAL_ORDER FROM Items_Order Group By ITEM )as od ORDER BY TOTAL_ORDER desc

Vijaydhas 11-Dec-13 13:10pm

It is also working..!!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ashish_Agrawal · Accepted Answer · 2013-12-11T06:44:00

Solution 1

How about this..

SQL

Select Top 3 Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc;

Also, you want total quantity..

SQL

SELECT TOP 3 item, Count(*) as Total_Order, Sum(quantity) as Total_Quantity 
FROM Items_Order
Group By item 
Order By Count(*) desc;

Posted 11-Dec-13 6:44am

Ashish_Agrawal

Updated 11-Dec-13 6:49am

v2

Comments

Vijaydhas 11-Dec-13 12:56pm

Super Gi.. Super Gi.. Super Gi.. It's Working.. I only concentrate in sub query. But now only i realize it is possible without Sup query..

Thanks for instant reply..

Vijaydhas 11-Dec-13 13:04pm

Can You give me query for this? Please..!!

Write a query to show total amount ordered for each month of each year. Column needed are month, year and total amount.