I've been using basic sql for a while and always used PHP to get round
complicated queries but now I need to see if I can create a query that
will show me the busiest day in our helpdesk and return the date and
the number of calls.

We have a database with a table called tracking which has date fields.
I want to count all teh records for each day, and return date with the
highest count and the count itself...

I'm sure its possible but I'm stuck as to what to google for...

Any ideas?

Thanks

ChrisJ

cjohnsonuk [at] googlemail.com wrote:
> I've been using basic sql for a while and always used PHP to get round
> complicated queries but now I need to see if I can create a query that
> will show me the busiest day in our helpdesk and return the date and
> the number of calls.
>
> We have a database with a table called tracking which has date fields.
> I want to count all teh records for each day, and return date with the
> highest count and the count itself...
>
> I'm sure its possible but I'm stuck as to what to google for...
>
> Any ideas?
>
> Thanks
>
> ChrisJ

I'm assuming tracking looks something like this:

tracking(call_id*,call_date)

* = PRIMARY KEY

Untested:

SELECT count(*) calls,call_date
FROM tracking
GROUP BY call_date
ORDER BY calls DESC
LIMIT 1;

strawberry wrote:
> cjohnsonuk [at] googlemail.com wrote:
> > I've been using basic sql for a while and always used PHP to get round
> > complicated queries but now I need to see if I can create a query that
> > will show me the busiest day in our helpdesk and return the date and
> > the number of calls.
> >
> > We have a database with a table called tracking which has date fields.
> > I want to count all teh records for each day, and return date with the
> > highest count and the count itself...
> >
> > I'm sure its possible but I'm stuck as to what to google for...
> >
> > Any ideas?
> >
> > Thanks
> >
> > ChrisJ
>
> I'm assuming tracking looks something like this:
>
> tracking(call_id*,call_date)
>
> * = PRIMARY KEY
>
> Untested:
>
> SELECT count(*) calls,call_date
> FROM tracking
> GROUP BY call_date
> ORDER BY calls DESC
> LIMIT 1;

Thanks That works great !
Except I forgot to say that the date field (called date) is a date and
time field so the grouping doesn't work as all the calls were placed at
different times ! Can we order just by the date part of the datetime
field?

ChrisJ

cjohnsonuk [at] googlemail.com wrote:
> strawberry wrote:
> > cjohnsonuk [at] googlemail.com wrote:
> > > I've been using basic sql for a while and always used PHP to get round
> > > complicated queries but now I need to see if I can create a query that
> > > will show me the busiest day in our helpdesk and return the date and
> > > the number of calls.
> > >
> > > We have a database with a table called tracking which has date fields.
> > > I want to count all teh records for each day, and return date with the
> > > highest count and the count itself...
> > >
> > > I'm sure its possible but I'm stuck as to what to google for...
> > >
> > > Any ideas?
> > >
> > > Thanks
> > >
> > > ChrisJ
> >

Worked it out
> > I'm assuming tracking looks something like this:
> >
> > tracking(call_id*,call_date)
> >
> > * = PRIMARY KEY
> >
> > Untested:
> >
> > SELECT count(*) calls,call_date
> > FROM tracking
> > GROUP BY call_date
> > ORDER BY calls DESC
> > LIMIT 1;
>
> Thanks That works great !
> Except I forgot to say that the date field (called date) is a date and
> time field so the grouping doesn't work as all the calls were placed at
> different times ! Can we order just by the date part of the datetime
> field?
>
> ChrisJ


Worked it out!

SELECT count(*) calls,date(`call_date`) as busyday
FROM tracking
GROUP BY busyday
ORDER BY calls DESC
LIMIT 1;