How would I write this query?

Below is a screenshot of the data that I'm working with. My challenge is to create a query that tells me which date has the greatest number of rows.

I was thinking that perhaps this might be a select within a select. Maybe something like select count(*)from (select id from calendar_look_alike group by date), but then I'm lost as far as how do I dictate that the only "count" I want is the one belonging to the date that the greatest "count" of rows?

What do you think?

data
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
You can try this
SELECT COUNT(*) AS total, DATE(date) 
FROM YOURTABLE
GROUP BY DATE(date)
ORDER BY COUNT(*) DESC LIMIT 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
Awesome, Julian! Thanks!
0
brucegustPHP DeveloperAuthor Commented:
By the way, is "DATE(date)" a form of shorthand? I was expecting that to be documented as "date" only, simply because that's the name of the field in the table. Is it a kind of alias?

Thanks, again!
0
Julian HansenCommented:
You are welcome,

By the way, is "DATE(date)" a form of shorthand?
No, the DATE() function returns the date portion of a datetime field so

DATE('2015-08-03 17:21:31')

Returns
2015-08-03

Refer
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
and
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
I did not know how you had defined your `date` field - looks like a date type but in case it was a datetime I used the DATE() function to ensure that the grouping was done only on the date part and not the time (if it existed). If the field is a date field then the function does nothing and is analogous to just using the date `field` on its own
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.