MYSQL Select Problem - Top 10 Selling Products 2

[kennygadams]

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

http://www.ImageEnvision.com/.

Here is the SQL statement that I have so far:

SELECT item_number FROM orders LIMIT 10

Kenny G. Adams

 

[pointertowhat]

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.

 

[r937]

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

 

[kennygadams]

Thanks pointertowhat, I could not figure out how to get the "ORDER BY count DESC" to work. I re-posted this question in the correct group so please post your response there.

http://www.tek-tips.com/viewthread.cfm?qid=1525578&page=1

Best regards,

Kenny G. Adams

 

[PHV]

SELECT item_number, COUNT(*) FROM orders GROUP BY item_number ORDER BY 2 DESC LIMIT 10

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kennygadams]

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

 

[r937]

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

 

[kennygadams]

Hi r937, I ran all three of the following queries and they all returned the same results. Which one should I use?

By r937...

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

By kennygadams...

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

By PHV...

SELECT item_number, COUNT(*) FROM orders GROUP BY item_number ORDER BY 2 DESC LIMIT 10

Best regards,

Kenny G. Adams

 

[r937]

mine

try ASC instead of DESC and you will see a difference

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[kennygadams]

I see! Thanks r937 You da man (or woman!)

Kenny G. Adams

 

[r937]

You da man (or woman!)

for you, kenny, i can be whatever you want me to be




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon