Math Not Adding up.. Not sure why.. 1

[Yrrk]

So I was pretty sure I had this one right. But it doesn’t work.. for some reason, one line DID work.. "H" came out right. It should really just be GBtoSafe/NumDays in the right 3 columns. (later two are multiplied by 7 and 30 respectively). Note for H, 419/33 is 12.69.. the others are WAY off.

ProjectName is a varchar
RestoreID,NumBytesToSafe are int(11)
TimeStarted is datetime
Active is an enum (Active or Inactive)

select RT.ProjectName,count(RT.RestoreID) as "Restores",TO_DAYS(CURDATE())-TO_DAYS(min(RT.TimeStarted)) as "NumDays", round((sum(RT.NumBytesToSafe)/1024/1024/1024),1) as "GB to Safe", ( (avg(RT.NumBytesToSafe)/1024/1024/1024) / (TO_DAYS(CURDATE())-TO_DAYS(min(RT.TimeStarted))) ) as "SafeGB/day", 7*( (avg(RT.NumBytesToSafe)/1024/1024/1024) / (TO_DAYS(CURDATE())-TO_DAYS(min(RT.TimeStarted))) ) as "SafeGB/week", 30*( (avg(RT.NumBytesToSafe)/1024/1024/1024) / (TO_DAYS(CURDATE())-TO_DAYS(min(RT.TimeStarted))) ) as "SafeGB/month"

from RestoreTable RT, ProjectTable PT

where RT.ProcessingStatus="ReadyForDrainToDS"

and PT.Active='Active'

and RT.ProjectName=PT.ProjectName

group by RT.ProjectName;



+----------------------+----------+---------+------------+-----------------+-----------------+------------------+

| ProjectName | Restores | NumDays | GB to Safe | SafeGB/day | SafeGB/week | SafeGB/month |

+----------------------+----------+---------+------------+-----------------+-----------------+------------------+

| A | 26 | 234 | 80.0 | 0.013147198961 | 0.092030392725 | 0.394415968820 |

| B | 1610 | 134 | 596.7 | 0.002765737607 | 0.019360163251 | 0.082972128219 |

| C | 2777 | 322 | 9194.9 | 0.010282852694 | 0.071979968861 | 0.308485580834 |

| D | 1807 | 333 | 5231.0 | 0.008693295259 | 0.060853066810 | 0.260798857758 |

| E | 13 | 61 | 51.5 | 0.064894924629 | 0.454264472400 | 1.946847738855 |

| F | 2 | 45 | 29.9 | 0.332403780468 | 2.326826463278 | 9.972113414047 |

| G | 1705 | 39 | 1884.3 | 0.028336718736 | 0.198357031154 | 0.850101562090 |

| H | 1 | 33 | 419.0 | 12.695973737081 | 88.871816159565 | 380.879212112420 |

| I | 3 | 214 | 2.7 | 0.004198088518 | 0.029386619627 | 0.125942655543 |

| J | 62 | 31 | 194.3 | 0.101110241622 | 0.707771691354 | 3.033307248661 |

+----------------------+----------+---------+------------+-----------------+-----------------+------------------+

 

[PHV]

Replace all avg by sum ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Yrrk]

argh!! I knew another pair of eyes would help standard cut/paste error. thanks!