CODEWITHSUNDEEP
mysql
Dorvalla
Dorvalla

Reputation: 5240

Mysql percentage calculation out of 1 column

Percentages in SQL have never been my strong suit while I know SQL is very good at calculating, so if i could get some help with this, it would be awesome.

Okay so the case is as following: I am trying to calculate the uptime of a server. So for now, I started with grabbing both values i need, which is the total and the downtime number

SELECT 
    COUNT(*) 'Total', 
    COUNT(IF(population = '0', 1, NULL)) 'Downtime' 
FROM  
    swg_servertracker

Output:

Total  Downtime
----------------
3735   332

So basically I check my total entries (which is filled by a cron job every 15 minutes) and checks if a server is online. If 0 is entered, I know the server is offline. This is placed in this query as Downtime.

So I figured I needed to flip this around, since I want uptime. 

SELECT 
    COUNT(*) 'Total', 
    COUNT(IF(NOT population = '0', 1, NULL)) 'Uptime' 
FROM 
    swg_servertracker

which returns

Total    Uptime
----------------
37351    37019

So how do I get the value of uptime on this in a percentage if I know these two in Mysql (I know this is roughly 99,1 percent looking at this, but just trying to calculate this in Mysql)

Upvotes: 0

Views: 157

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Simply need to use these expressions together to calculate the percentage:

SELECT COUNT(*) AS Total, 
       COUNT(IF(population<>'0', 1, NULL)) AS Uptime, 
       100 * COUNT(IF(population<>'0', 1, NULL)) / COUNT(*) AS Percent_Uptime
FROM swg_servertracker

Also, avoid using single quotes around the aliases/field names. For more understanding, please check: When to use single quotes, double quotes, and backticks in MySQL

If you want to round the percent value to (let's say) 1 decimal place, you can use ROUND() function:

SELECT COUNT(*) AS Total, 
       COUNT(IF(population<>'0', 1, NULL)) AS Uptime, 
       ROUND(100 * COUNT(IF(population<>'0', 1, NULL)) / COUNT(*), 1) AS Percent_Uptime
FROM swg_servertracker

Additional Tip: You can also write the query utilizing SUM() function and MySQL's implicit typecasting of booleans to 0/1:

SELECT COUNT(*) AS Total, 
       SUM(population <> '0') AS Uptime, 
       ROUND(100 * SUM(population <> '0') / COUNT(*), 1) AS Percent_Uptime
FROM swg_servertracker

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133360

You could use both the columns you have alredy obtained for the rate .. 

  SELECT COUNT(*) Total
      , COUNT(IF(NOT population='0', 1, NULL)) Uptime 
      , (COUNT(*)/COUNT(IF(NOT population='0', 1, NULL)))*100 my_perc
  FROM swg_servertracker

Upvotes: 0

Related Questions