Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MYSQL Select Problem - Top 10 Selling Products 2

Status
Not open for further replies.

kennygadams

Programmer
Jan 2, 2005
94
US
Any help is very much appreciated!

I have an "orders" table with an "item_number" field which has many duplicate records.

I want to select the top ten duplicated item numbers in this orders table and would also like to get the total number of times each record was found in the table.

I'm putting together a dynamic webpage, with PERL and MYSQL, which lists the top 10 selling images at
Here is the SQL statement that I have so far:

Code:
SELECT item_number FROM orders LIMIT 10

Kenny G. Adams
 
try something like

select item_number, count(*) from orders group by item_number

You can then refine this with ASC and DESC and LIMIT to get tht etop 10 sellers, the bottom 10 sellers etc.
 
for future questions about your MySQL database, please don't use this forum, use forum436

(MySQL SQL is often substantially different from ANSI SQL)

:)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
SELECT item_number, COUNT(*) FROM orders GROUP BY item_number ORDER BY 2 DESC LIMIT 10

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks PHV, that worked.

Here is another way to get the top ten duplicated item_number records:

select item_number, count(*) as cnt FROM orders GROUP BY item_number ORDER BY cnt DESC LIMIT 10

Kenny G. Adams
 
yeah, but kenny, that solution as well as PHV's solution are incomplete, because they do not encompass a certain portion of your requirement -- "top ten duplicated item numbers"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Hi r937, I ran all three of the following queries and they all returned the same results. Which one should I use?

By r937...
Code:
SELECT item_number
     , COUNT(*) AS daCount
  FROM orders
GROUP
    BY item_number
HAVING daCount > 1
ORDER
    BY daCount DESC LIMIT 10

By kennygadams...
Code:
select item_number, count(*) as cnt FROM orders GROUP BY item_number ORDER BY cnt DESC LIMIT 10

By PHV...
Code:
SELECT item_number, COUNT(*) FROM orders GROUP BY item_number ORDER BY 2 DESC LIMIT 10

Best regards,

Kenny G. Adams

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top