Hello Guys, My problem is i don't know how to query TOP ORDER PROD using COUNT,AVG...
anybody can help me please? thanks ^_^

EXAMPLE

-LIST OF ORDER'S-
ITEM CODE | ITEM NAME | 
4323                  PROD A 
4323                  PROD A 
4323                  PROD A 
4323                  PROD A 
4323                  PROD A 
2345                   PROD B
2345                   PROD B
2345                   PROD B
3462                   PROD C
3462                   PROD C

i don't know how display TOP ORDER PROD. using count and average

-TOP ODER PRODUCT-
ITEM CODE | ITEM NAME | TOTAL NO. OF ORDERS | PERCENTAGE RATE
4323                   PROD A                  5                 4.23%
2345                   PROD B                  3                  2.33%
3462                   PROD C                  2                  1.56%

You could do something like this to get an aggregate of items, orders, and count:

SELECT 
    ItemCode, 
    ItemName, 
    COUNT(*) AS Orders
FROM [I]TableName[/I]
GROUP BY ItemCode, ItemName
ORDER BY Orders DESC

To add the percentage you could either cache a total before hand and use a variable to calclulate it, or do this:

SELECT 
		ItemCode, 
		ItemName, 
		COUNT(*) AS Orders,
		(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM [I]TableName[/I]))
	FROM [I]TableName[/I]
	GROUP BY ItemCode, ItemName
	ORDER BY Orders DESC

Thx Mr. CodeWord it works ^_^

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.