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.