Twitch Posted March 18, 2014 Share Posted March 18, 2014 Hello Fellow Freaks, I have written two simple queries to grab some totals. This query successfully gets the subtotal of each row based on the rate and the amount of minutes select slip_rate AS RATE_PER_HOUR,ROUND( ( SUM( TIMESTAMPDIFF( MINUTE , slip.slip_start, slip.slip_end ) ) ) ) AS MINS,ROUND( ( SUM( TIMESTAMPDIFF( MINUTE , slip.slip_start, slip.slip_end ) ) /60 ) * slip_rate, 2 ) AS SUB_TOTAL FROM slip WHERE project_id=3 GROUP BY slip_id The above code returns:RATE_PER_HOUR | MINS | SUB_TOTAL 65.00 | 135 | 146.25 25.00 | 120 | 50.00 65.00 | NULL | NULL This one is almost what I want... select slip_rate AS RATE_PER_HOUR,ROUND( ( SUM( TIMESTAMPDIFF( MINUTE , slip.slip_start, slip.slip_end ) ) ) ) AS MINS,ROUND( ( SUM( TIMESTAMPDIFF( MINUTE , slip.slip_start, slip.slip_end ) ) /60 ) * slip_rate, 2 ) AS TOTAL FROM slip WHERE project_id=3 The above code returns:RATE_PER_HOUR | MINS | TOTAL 65.00 | 255 | 276.25 As you can see the mins are correct however it is calculating all of them at the 65.00 rate. All I wish to do is write a simple query that will return TOTAL196.25 I'm sure I'm close I just haven't messed much with the SUM and TIMESTAMPDIFF function so any help would be greatly appreciated. Thanks in advance,Twitch Link to comment https://forums.phpfreaks.com/topic/287040-simple-total-query-not-working/ Share on other sites More sharing options...
Twitch Posted March 18, 2014 Author Share Posted March 18, 2014 Ok I knew I was close I guess I just needed some sleep...haha Here is the query that gets what I want in case someone else needs it: SELECT ( ROUND(SUM(TIMESTAMPDIFF( MINUTE , slip_start, slip_end ) /60*slip_rate),2)) AS TOTAL FROM `slip` WHERE project_id=3 Link to comment https://forums.phpfreaks.com/topic/287040-simple-total-query-not-working/#findComment-1472950 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.