Query help: summing colums and rows
Date: 07/21/05
(MySQL Communtiy) Keywords: no keywords
Query I have now that's that has bad syntax
$Query = "SELECT sum((end_time - start_time) * rate) AS subtotalA, invoice_number GROUP BY invoice_number FROM invoice_items HAVING office_id='$office_id' AND service_type!='225' ORDER BY invoice_number"
The data may have several rows with the same invoice number. I want to pull up a subtotal per invoice number as a result array. However, that subtotal is the result of 3 columns: (end_time - start_time) * rate
I am looking around the manual right now.
Thanks for any insight. I am inexperienced with these complex queries.
Sub isssue: time values and math.
OK, apparently, the query statement is not recognizing start_time and end_time as time values and is hanfdling them as time values. AAm I overl;ooking a function that will accomplish this?
BTW my time values are hh:mm:ss
My solution for the second part is TIME_TO_SEC() and then do the math with all seconds and then convert back to hours.
Source: http://www.livejournal.com/community/mysql/62701.html