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
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?
Thanks.
-Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
