Jump to content

Creating queries to return 'trivia' based stats


creospace

Recommended Posts

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.