Max of each count

[jeppius]

Hi,
I spent the whole day trying to figure out a valid mysql query which includes MAX and COUNT and the same time: suppose I have the following table recording some bought items

ITEM TYPE
banana fruit
banana fruit
apple fruit
carrot vegetable
tomato vegetable
tomato vegetable
tomato vegetable

I need to retrieve the maximum number of hits for each TYPE: something like
fruit 2 banana
vegetable 3 tomato


Still I can't imagine a single query to do all the job, any hints?
Thanks in advance should someone be inspired enough ;)
Josep

[mwasif]

This will work for you

SELECT * FROM (
    SELECT type, COUNT(*) AS cnt, item FROM table_name
    GROUP BY item
    ORDER BY cnt DESC
    ) tmp 
GROUP BY type

[jeppius]

Hey many thanks!
I get the idea, not sure if it would be too hard just to display the max item count from each type, as this query makes the count for item, orders it and groups it by type, but displays all the items...the problem is just to display the item with the highest count from each type...

This will work for you

SELECT * FROM (
    SELECT type, COUNT(*) AS cnt, item FROM table_name
    GROUP BY item
    ORDER BY cnt DESC
    ) tmp 
GROUP BY type

[mwasif]

Did you try this query? What do you get when you execute this query?

[jeppius]

Well I get a
mysql_fetch_array(): supplied argument is not a valid MySQL result resource.
Maybe I should group by type in the inner query too?

Did you try this query? What do you get when you execute this query?
Originally Posted by mwasif
This will work for you

SELECT * FROM (
    SELECT type, COUNT(*) AS cnt, item FROM table_name
    GROUP BY item
    ORDER BY cnt DESC
    ) tmp 
GROUP BY type

[mwasif]

Before using this query in your PHP code, execute this query in MySQL and see if it gives you the required results. And replace table_name with your actual table name and column names too.

[amitpatel66]

Hi,
I spent the whole day trying to figure out a valid mysql query which includes MAX and COUNT and the same time: suppose I have the following table recording some bought items

ITEM TYPE
banana fruit
banana fruit
apple fruit
carrot vegetable
tomato vegetable
tomato vegetable
tomato vegetable

I need to retrieve the maximum number of hits for each TYPE: something like
fruit 2 banana
vegetable 3 tomato


Still I can't imagine a single query to do all the job, any hints?
Thanks in advance should someone be inspired enough ;)
Josep

SELECT E.type,MAX(E.cnt_item) FROM
(SELECT item,COUNT(item) cnt_item,type from <table_name> GROUP by item,type) E
GROUP BY E.type
 

[jeppius]

Wow perfect, that did the job!
looks surprisingly simple once worked out...very elegant!
Many thanks guys :)

Josep M

SELECT E.type,MAX(E.cnt_item) FROM
(SELECT item,COUNT(item) cnt_item,type from <table_name> GROUP by item,type) E
GROUP BY E.type