Christopher Molnar <[EMAIL PROTECTED]> wrote on 06/27/2005 12:55:08 PM:
> I have a table that has the following structure: > mysql> describe time_sheet; > +------------------+---------------+------+-----+------------ > +----------------+ > | Field | Type | Null | Key | Default | > Extra | > +------------------+---------------+------+-----+------------ > +----------------+ > | tdate | date | | | 2000-01-01 > | | > | callslip | text | | | > | | > | customer | text | | | > | | > | time_in | time | | | 00:00:00 > | | > | time_out | time | | | 00:00:00 > | | > | item_sold | text | YES | | NULL > | | > | amount | decimal(10,2) | YES | | NULL > | | > | citem_sold | text | YES | | NULL > | | > | camount | decimal(10,2) | YES | | NULL > | | > | amount_collected | decimal(10,2) | YES | | NULL > | | > | commision | decimal(10,2) | YES | | NULL > | | > | tsid | int(11) | | PRI | NULL | > auto_increment | > | dispatch_time | time | | | 00:00:00 > | | > | ctype | text | YES | | NULL > | | > +------------------+---------------+------+-----+------------ > +----------------+ > 14 rows in set (0.00 sec) > I am trying to use the following select statement: > select ctype, count(tsid), sum(amount), sum(amount_collected), sum > (camount), sum(commision), subtime(time_out,dispatch_time) as time > from time_sheet where tdate='2005-06-22' group by ctype; > It gives me the following results: > +-------+-------------+-------------+----------------------- > +--------------+----------------+----------+ > | ctype | count(tsid) | sum(amount) | sum(amount_collected) | sum > (camount) | sum(commision) | time | > +-------+-------------+-------------+----------------------- > +--------------+----------------+----------+ > | CMP | 1 | 0.00 | 0.00 | > 0.00 | 0.00 | 01:15:00 | > | INS | 2 | 0.00 | 0.00 | > 0.00 | 0.00 | 03:00:00 | > | PMNR | 1 | 0.00 | 0.00 | > 0.00 | 0.00 | 01:30:00 | > | SC | 1 | 0.00 | 0.00 | > 0.00 | 0.00 | 01:45:00 | > +-------+-------------+-------------+----------------------- > +--------------+----------------+----------+ > 4 rows in set (0.05 sec) > > The Time column at the end should not just be for 1 entry - it needs > to show the time_out minus the dispatched time for the group. > In other words if in the INS calltype I have 2 calls that: > Dispatch Time Time_out > 12:00 14:00 > 14:00 15:00 > My total Time Column should read 3:00 Isn't that exactly what you posted? | INS | 2 | 0.00 | 0.00 | 0.00 | 0.00 | 03:00:00 | That last column represents 3 hours, does it not? You have me confused. > What I would like to use is: > > select ctype, count(tsid), sum(amount), sum(amount_collected), sum > (camount), sum(commision), sum(subtime(time_out,dispatch_time)) as > time from time_sheet where tdate='2005-06-22' group by ctype; > NOTE: This adds a SUM() to the time column. > Is this do-able - (doesn't work this way - maybe in another manner? That would have worked if TIME values weren't converted to numbers by packing their components together. (This is one of my very few peeves with MySQL. Packing works well for sorting but not at all for date math). To get good date math working, you need to use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert your time differences into and out of actual numeric values (ones that are SUM()-able). http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html You could also use time_to_sec() and sec_to_time() to do the same down-conversion/restoration. (Any pair of functions that convert the a time into some kind of numeric value and back will work but these come to mind first.) > Thanks. > -Chris Try this: SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime(time_out,dispatch_time)))) as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; -- or -- SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , SEC_TO_TIME(sum(TIME_TO_SEC(subtime(time_out,dispatch_time)))) as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
