Mysql percentage calculation out of 1 column

Question

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)

Madhur Bhaiya · Accepted Answer · 2019-09-04T10:57:19.147

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

Cheers, thats what i am looking for , just one minor question, it rounds now at 4 decimals, is it possible to get it 1? — Dorvalla, Sep 04 '19 at 10:55
@Dorvalla yes you can, please check the updated answer. Thanks. — Madhur Bhaiya, Sep 04 '19 at 10:56

score 0 · Answer 2 · answered Sep 04 '19 at 10:53

0

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

answered Sep 04 '19 at 10:53

ScaisEdge

131,976
10
91
107

Mysql percentage calculation out of 1 column

2 Answers2