Creating queries to return 'trivia' based stats

[creospace]

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

[jworisek]

without more info...

something like this maybe?

SELECT sum(appointments) as num_appointments, user,.... FROM .... WHERE date='xxxx-xx-xx' and.... GROUP BY user ORDER BY num_appointments ASC LIMIT 1;

[wickning1]

Usually when you want to find the highest result(s), you do an ORDER BY and LIMIT. As jworisek pointed out, you'll probably need to combine that with a GROUP BY query in order to get the statistic you're looking for.

We can help you out with details if you ask a specific question. Give us the table structure and a query you'd like to do.

[creospace]

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

[fenway]

My tired eyes can't help you with the queries at this wee hour of the morning; I'm sure the others will be more helpful. Just a few comments:

I can't believe your date column is called date... don't used reserved keywords -- they're reserved for a reason! [i]<rant> I wish PHPMyAdmin would stop putting everything in backticks all the time. </rant>[/i]

Also, your default value choices are interesting, as is your decision to store the handler name given that you have a uid already. And a datestamp and a date field?

Don't mean to be annoying... I'm just concerned about these things, and I don't want to give you incorrect queries. It shouldn't be difficult with a few GROUP BY and COUNT() statements.

[creospace]

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

[wickning1]

The highest number of calls in a day was XX by XX (agent) on dd-mm-yyyy:
[code]SELECT MAX(handlername) as agent, COUNT(*) as calls, date FROM tblprospects GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1[/code]
The highest number of appointments in a day was XX by XX (agent) on dd-mm-yyyy
[code]SELECT MAX(handlername) as agent, COUNT(*) as appts, date FROM tblprospects WHERE appointment=1 GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1[/code]
Actually having both the date and datestamp may help MySQL to index these groupings. Grouping on a function like DATE(datestamp) might break indexing. I need to test this though.

Make sure you create an index on (handlerID, date) to help optimize these. By that I mean a multiple column index, NOT two indexes! If you already have an index on handlerID, just add date to it.

[creospace]

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

[wickning1]

Like I said, DATE(datestamp) = date in your tbl_prospects. So in your other table, you can do:
SELECT DATE(datestamp) as date ... GROUP BY date

I just don't know if it will use your datestamp index, so it may be a slow query.

[creospace]

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

[creospace]

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

[wickning1]

Yeah, looks good to me. It just might get slow when your appointments table is really big. You may want to do the query once a day and store the answer, or something like that. Or go with the second column for just the date part - it's ugly but it should work.

I really need to test whether MySQL is smart about this, it seems like a very common thing to GROUP on.

[creospace]

Doesn't work :(

oh well back to the drawing board :)

thanks for everyones help :) :)

Gary

[fenway]

Doesn't work how?