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 ... 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. Thank you. -------------------------------------------------------- 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.
