Performing MATH on database query results

Hello,

I have an page that is doing a query on a database and returning the results to the screen. The end result I need is that the page will send an email that has two columns, one for element name, and one for the calculated percentage. Essentially, I have the following...

I get a list of records that contain a timedate value and a value of trur or false in a col called 'value' These are up/down alerts for various servers(elements) I need to calculate to 4 decimal places, the amount of uptime for the reported time period and send that out via email. Can anyone help? Thanks!!!

Joe

Hi Joe,

I'm not sure we have enough information, but assuming that true is up and false is down, you could do a

SELECT COUNT(*) FROM theTable WHERE theDate between(date1 AND date2) AND Value = true

...(or 1, in some DBs). By the way, Value is probably not a good field name -- reserved words should never be used to name database objects -- but that's another issue.

Once you have a count of the true records and a count of the false records, it's easy to take those two numbers and divide the true number by the sum of the true and false checks. That's your percentage, though you can't make it accurate to 4 decimal places unless you have a big enough number (at least a hundred thousand "up" values).