Creating queries to return 'trivia' based stats
Posted 04 April 2006 - 04:16 PM
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
Posted 04 April 2006 - 07:10 PM
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;
Posted 04 April 2006 - 11:20 PM
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.
Posted 05 April 2006 - 06:51 AM
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.
Posted 05 April 2006 - 07:27 AM
I can't believe your date column is called date... don't used reserved keywords -- they're reserved for a reason! <rant> I wish PHPMyAdmin would stop putting everything in backticks all the time. </rant>
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.
Posted 05 April 2006 - 08:04 AM
Posted 05 April 2006 - 12:18 PM
SELECT MAX(handlername) as agent, COUNT(*) as calls, date FROM tblprospects GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1The highest number of appointments in a day was XX by XX (agent) on dd-mm-yyyy
SELECT MAX(handlername) as agent, COUNT(*) as appts, date FROM tblprospects WHERE appointment=1 GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1Actually 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.
Posted 05 April 2006 - 12:23 PM
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
Posted 05 April 2006 - 12:29 PM
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.
Posted 05 April 2006 - 01:31 PM
SELECT count(*) as num_apts, DATE(datestamp) as date FROM tblappointments GROUP BY date ORDER BY num_apts DESC LIMIT 1
Thanks for your help so far it's been very helpful indeed
Posted 05 April 2006 - 05:01 PM
I really need to test whether MySQL is smart about this, it seems like a very common thing to GROUP on.
Posted 07 April 2006 - 10:31 AM
oh well back to the drawing board
thanks for everyones help
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users