Jump to content

Simple total query not working


Twitch

Recommended Posts

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 
 
TOTAL
196.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

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


Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.