creospace

Doesn't work :( oh well back to the drawing board :) thanks for everyones help :) :) Gary

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

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

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

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

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

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