[SOLVED] query help

[jake2891]

Hi Guys,

 

Can anyone help me figure out how to get a specific result please. Basically i have a table containing property id's. I need to work out which property id appears in the table the most and count this.

 

example table below

// notice 3 property ids are the same

 

user_id    property_id

1            1

2            1

3            1

4            2

5            3

 

So property id 1 appears the most, so i need to count all the property ids, determine out which one has the most of the same property ids and then total how many property ids the one that appears the most has. eg 3

 

I was thinking

 

'SELECT count(*) from foo AS tot GROUP BY property_id';

 

then selecting MAX  but abit lost.

[akitchin]

column aliases get places right after the field to be aliased, first of all.  second, you need to select the column that you're grouping by, if i'm not mistaken:

 

SELECT COUNT(*) AS total, product_id FROM foo GROUP BY property_id ORDER BY total DESC LIMIT 1

 

give this a shot.  i don't know whether you can use an aggregate function result as an ORDER BY field, but give it a shot.  not sure about using MAX(), but it could work:

 

SELECT MAX(COUNT(*)), product_id FROM foo GROUP BY property_id

 

my guess would be that the latter will not work.

[jake2891]

Thanks for your help will give it a try Hopefully it solves my problem.