Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0
But it still is "all" the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the "right" numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? Thanks, Craig -------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -----Original Message----- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 8:53 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Hi Craig, Weston, Craig (OFT) wrote: > Hello everyone. > > Once again, I am jousting at the windmill of time and date formulae > within MYSQL. I seek to create a cross-tab or pivot table of the SUM of > all times with a specific category, on a per-ticket basis. I have > everything working except the math part. Even that is kind of working > ok, but it is not adding up The math part is: > > > > IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP' > > > > (the same formula with 'CLOSED','OPEN', etc lists all the various > statuses available.) > > > > CLOCK_TIME is a varchar field that contains a 4 digit date counter and a > timer, in the format of > > > > 0293 23:44 > > 0001 00:29 > > 0001 19:15 My hunch is this is the problem. You should split the field into two: one for the date counter, one for the time. time_to_secs() is probably returning zero for most of these. > > Now, I run the query and get results. Every ticket has more than one > status. But, for each ticket, I get a single line that appears to have > the entire ticket time (in seconds) in one field and the rest are zero. > > > > I think this tells me that the statement is working but that I am > grouping them wrong? `key` is the ticket number. > > > > From > > `clock_data` > > group by > > `clock_data`.`key` > > > > So my result set looks like > > > > 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0 > > 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0 > > > > Etc. > > Can anyone help me over this hill? I think I am writing the if statement > incorrectly somehow but don't see a way out of the box yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
