[Creating queries to return 'trivia' based stats]

Doesn't work :(

oh well back to the drawing board :)

thanks for everyones help :) :)

Gary

[Creating queries to return 'trivia' based stats]

Would this be right then?

[code]SELECT count(*) as num_apts, DATE(datestamp) as date FROM tblappointments GROUP BY date ORDER BY num_apts DESC LIMIT 1[/code]

Thanks for your help so far it's been very helpful indeed :)

Gary

[Creating queries to return 'trivia' based stats]

Ps: i need to learn more about indexing as well :)

[Creating queries to return 'trivia' based stats]

Hio There,

Yes looks like we came to the same conclusion here's what i've just done.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT count(*) as num_calls, date, handlername FROM tblprospects GROUP BY date, handlername ORDER BY num_calls DESC LIMIT 1[/quote]

However as you rightly poitn out teh date works and the datestamp does not and I need to work out how to use a date stamp as I have another table to work with that has datestamp only :(

Cheers

Gary

[Creating queries to return 'trivia' based stats]

Hi there, i knew someone would comment on my sloppy set up, it was done a while ago originally and i've been on a steep learning curve but havent changed anything :)

My bad :(

Gary

[Creating queries to return 'trivia' based stats]

Ok thank you for your kind offer, here is the table structure
tblprospects (
prospectID int(11) DEFAULT '' NOT NULL auto_increment, - row number
handlerID int(11) DEFAULT '0' NOT NULL , - agent /user id
handlername text , - agents name
companyname text , - prospect detail
trade text , - prospect detail
contactname text , - prospect detail
telnumber text , - prospect detail
town text , - prospect detail
county text , - prospect detail
answer tinyint(2) , - flag 0=no, 1=yes
voicemail tinyint(2) , - flag 0=no, 1=yes
pitch tinyint(2) DEFAULT '0' NOT NULL , - flag 0=no, 1=yes
appointment tinyint(2) , - flag 0=no, 1=yes
callback tinyint(2) , - flag 0=no, 1=yes
comments text ,
datestamp timestamp(14) ,
date date ,
salesmanID smallint(4) , for whom they are researching leads for
PRIMARY KEY (prospectID),
KEY prospectID (prospectID)
);

So each prospect called has a result, should the prospect go on to make an appointment there and then the appointment field is marked 1 and then the system goes off somewhere to make an appointment.

So i want to do some trivia stats as mentioned above for instance:

The highest number of calls in a day was XX by XX (agent) on dd-mm-yyyy etc or
The highest number of appointments in a day was XX by XX (agent) on dd-mm-yyyy etc

I hope hat makes it clearer, i sat and thought about it last night and began to wonder that i might have to use some elopement of php as well as the sql result to get to my final desired result but I’m not sure.

Many thanks
Gary

[Creating queries to return 'trivia' based stats]

Hi,

I have a db that holds data on telesales results. I've given the mainuser a fairly extensive statistical over view but I want to offer some trivia stats like 'The best day was xx when xx got xx appointments' or the highest number of calls was made on xx by xx with xx number of calls etc'

I really can't get my head around how I build the query for this type of thing. Each row contains info on the date, result, agent etc.

Anyone got any ideas ?

Thank you in advance of your assistance

Gary