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?

Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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) 

Open in new window

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
Bruce GustPHP DeveloperAuthor Commented:
Awesome, Julian! Thanks!
Bruce GustPHP 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!
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')


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
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

From novice to tech pro — start learning today.