[SOLVED] Need help determining most frequent number in a field

[Craig79]

Hello,

 

I have a column of integers that are all multiples of 5. How do I determine which number in the field occurs most frequently?

 

Thank you!

[Gamic]

select * from(
select numberField, count(*) as countOfUse
from table
group by number
)tmp
order by tmp.countOfUse desc

[Craig79]

Thanks Gamic,

 

I've never seen a query like that! I tried it but I couldn't get it to work. Here's what I did:

 

$SQL = "select * from(select my_state, count(*) as countOfUse from t_locations group by my_price)

tmp order by tmp.countOfUse desc";

 

The "my_state" field in the "t_locations" table consists of integers. These integers stand for USA state codes.  The "my_price" field is in the same table and it also consists of integers (dollar values rounded up). I'm passing in the state code from a PHP script to find the most commonly paid price for that state (but not the average). So, the puzzle is to find the most commonly paid price where the state code = 65.

 

Could you please clarify the query?

Thank you!

[Gamic]

<?php
$SQL = "
select * from(
select 
	my_state, 
	count(*) as countOfUse 
from t_locations 
group by my_state
)tmp 
order by tmp.countOfUse desc";
?>

 

This query tells you how many times a value of my_state appears in t_locations.

 

To get the most commonly paid price for a state you would do something similar.

selet * from(
select
	my_price,
	count(*) as countOfUse
from t_locations
where my_state = 65
group by my_price
)tmp order by tmp.countOfUse desc

[Craig79]

That's it! I tried researching how to do it but didn't see anything like that.

 

Thank you Gamic!