I Need MySQL Help.

[thenext88]

I have a database with information stored, and one of the columns of data is "data_date" in "YYYY-MM-DD" format. Some dates have multiple rows of data. How can I select the most frequently occurring date? I want to figure out which date is most "popular", return that date, and how many rows there are of that date, without ever looking at the database itself.

So...

$query = mysql_query("SELECT * FROM $db ORDER by data_date ASC");
// now how do I do what I want here?

Basically I do not know how to group a selection by column, and then count the largest group.

Will rep for help. Thanks.

 

[user-7256]

without ever looking at the database itself.

It's impossible to get data from it without querying it.

Also, your MySQL statement should not be SELECT * FROM $db, if "$db" holds your database name. It needs to hold a table name.

A classic way of doing it is to just count each value you pull from the rows using an array and figure out which value has the most hit counts... it's a bit slow, though.

I'm sure there's a SQL command to do this? I don't know what it is...

 

[enlytend]

This is my Before Coffee brain talking, but this might do the trick ...

SELECT date, count(*) FROM yourtablename GROUP BY(date);

If you want the highest count or your want to order the results you probably need to alias the count(*) and add an ORDER BY on the alias

SELECT date, count(*) AS foo FROM yourtablename GROUP BY(date) ORDER BY foo DESC;

(I may revise this post After Coffee, but that should set you on the right track )

 

[eagle12]

This is the way that I would do it

SELECT data_date, COUNT( * ) AS date_count 
FROM tablename 
GROUP BY data_date 
ORDER BY date_count DESC 
LIMIT 1

 

[thenext88]

Thanks for the help everyone. Throwing the count into the query (and ordering by the count) is what did the trick. Pulling the first result (limiting to 1 row) gave me what I needed.

Thanks again!