How to compute the sum?

raffielim

hi guys... i need help in computing something...
I have a database wherein i have in there the reports of my co-workers for everyday report... what i am trying to do is make a database wherein users can choose from what day up to what day they want to see the number of calls my co-workers did... but the problem is i dunno how to compute the sum of calls they did per day... e.g.

search from JANUARY 1 2005 up to JANUARY 12 2005.

it would show summaries of our total calls each day... is this possible? thanks

Shrike

Without a little bit of information about the database schema, it's impossible to say exactly what you need to do. If you assume that each database entry has a user ID, and a timestamp, then the query is pretty simple.

// Assume a few variables
// $datefrom (a MySQL timestamp e.g. 20050101000000)
// $dateto another timestamp
// $userid the User's ID to search for
SELECT COUNT(userid) AS numreports
FROM table
WHERE timestamp > '$datefom'
AND timestamp < '$dateto'
AND userid = '$userid'

If you can elaborate a little, a more accurate reply can be made.

devinemke

just guessing here at your exact data structure:

SELECT SUM(calls) FROM table WHERE date BETWEEN '2005-01-01' AND '2005-01-12'

raffielim

Here is the sample input in the database.I want to compute for the sum of the four agents(paz,maan,cherry,john) per day(2005-01-05,2005-01-06,2005-01-07) arranged horizontally and again get the sum of of the result of that(it's from 2005-01-05,2005-01-06,2005-01-07, so there would be 3 horizontal sum) vertically. Is that possible to get two sum having these inputs? If possible, how can i do this? thanks!

2005-01-05 Paz 4 3 2 1 7/0 6/0 5/0 4/0 3/0 2/0 1/0 10 9 8 7 6 5 4 3 2 1
2005-01-05 Maan 4 3 2 1 7/0 6/0 5/0 4/0 3/0 2/0 1/0 10 9 8 7 6 5 4 3 2 1
2005-01-05 Cherry 4 3 2 1 7/0 6/0 5/0 4/0 3/0 2/0 1/0 10 9 8 7 6 5 4 3 2 1
2005-01-05 John 1 1 1 1 1/0 1/0 1/0 1/0 1/0 1/0 1/0 1 1 1 1 1 1 1 1 1 1
2005-01-06 John 2 22 11 22 44/0 33/0 11/0 55/0 33/0 55/0 166/0 1 5 4 5 0 0 0 0 0 0
2005-01-06 Maan 56 0 21 22 3/0 5/0 6/0 1/0 4/0 6/0 24/0 1 3 3 5 0 0 0 0 0 0
2005-01-06 Cherry 33 33 13 55 6/0 44/0 5/0 2/0 6/0 27/0 7/0 6 1 3 5 6 6 0 0 0 0
2005-01-06 Paz 1 2 3 4 0/0 0/0 0/0 0/0 0/0 0/0 0/0 0 0 0 0 0 0 0 0 0 0
2005-01-07 Maan 1 1 1 1 1/1 1/1 1/1 1/1 1/1 1/1 1/1 1 1 1 1 1 1 1 1 1 1
2005-01-07 Cherry 1 1 1 1 1/1 1/1 1/1 1/0 0/0 0/0 0/0 0 0 0 0 0 0 0 0 0 0
2005-01-07 John 42 2 2 4 4/2 2/4 4/2 5/4 2/3 5/5 5/2 5 2 5 2 5 2 5 2 5 2
2005-01-07 Paz 1 0 1 0 0/1 0/0 1/0 1/0 0/1 0/0 1/0 0 1 0 0 0 0 0 0 0 0

devinemke

i still don't quite undertsand your data structure. what are all the numbers following the agent names? is that all one field or are they separate fields separated by the slashes?

raffielim

Hi... each number is a different entry.

AstroTeg

Is "0/1" in just one field? If so, you'll have a really hard time to get SQL to do the math for you (you'd have to resort to PHP and looping the recordset and calculating the totals). And you might think about saving that data in different fields or tables.

raffielim

hi...0/1 is not inside one field... those are 2 seperate variables... i just connected them because the report needs to compare number of calls against dropped calls... all the numbers that you see in there are all individual variables.

Roger_Ramjet

It looks horrible!! Good luck with this one devinmke and shrike.

(looks like a job for the GROUP BY and aggregate functions to me)

AstroTeg

Hmm... Sounds like a cross-tab report to me. You're taking the totals of all people and comparing them in one report.

I just did a quick google search and this came up: http://www.evolt.org/article/Using_MySQL_and_PHP_to_Present_Excel_Spreadsheets/20/26896/

That might get you started. There was a devshed article which I used to work on my reports and it was pretty good.

Roger_Ramjet

Can you do crosstabs in mysql? Havn't come across that in the manual.

Hm! Nice link AstroTeg. Yes, you can fake a crosstab in mysql, great.