[Resolved] SUM time in multiple rows

cassius

Hi.
I'm making an invoice system for my company and need to know how to add several time-fields from my mysql-database.

I have a database with fields ID, ASub(ph.number), Time(time used on the phone), value(std. costs) for using the system.

Each time a user makes a call from his phone, the system makes an entry to the database with the users phonenumber and the duration/time of the call.

I want to sum up the duration of all the calls for each selected user in the database, but i cannot get a query that gives me the correct result.

I use this query by now:

$datasql = "SELECT ASub, Time, SUM(Value) subtotal FROM test GROUP BY ASub;";

Anyone who could give me a hint on this one?
I tried SUM(Time), but that didn't work...and I understood why after reading the mysql-manual.

Please help me 🙂

mrhappiness

Don't use an ; in your query
Don't refer to the time column as it's not neccessary here

btw: why can't you use the time column?

cassius

I have left out my ; (my mistake), but that doesn't change the result.

I can use the time column, but if the user makes 5 phonecalls with a duration of 00:01:00(1 minute) i want the query to return 00:05:00(the sum of the time column in every row that has calls made from the same phonenumber).

Hope this clears up my problem/issue.

kpzani

why not extract the hours, minutes and seconds from the field, then sum these separately then add them back together. might be easier to do the adding back together in php later.

mrhappiness

Why not look at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html and find something like this:

SELECT
  pc_number, SEC_TO_TIME(SUM(TIME_TO_SEC(pc_duration))) duration
FROM phonecalls
GROUP BY pc_number

CREATE TABLE phonecalls (
  pc_id int(10) NOT NULL auto_increment,
  pc_number int(5) NOT NULL default '0',
  pc_started datetime NOT NULL default '0000-00-00 00:00:00',
  pc_duration time NOT NULL default '00:00:00',
  PRIMARY KEY  (pc_id)
) TYPE=MyISAM;

INSERT INTO phonecalls VALUES 
(1, 12345, '2005-03-29 11:45:56', '00:01:00'),
(2, 12345, '2005-03-29 11:46:14', '00:01:45'),
(3, 44444, '2005-03-29 11:46:31', '00:02:00'),
(4, 12345, '2005-03-29 11:46:47', '00:01:25'),
(5, 44444, '2005-03-29 11:47:03', '00:58:00');

should return 00:04:10 for 12345 and 01:00:00 for 44444

cassius

Thank you. That worked for me 🙂

Tnx a lot for your help to the both of you!