1

I have a table called server_connections which adds a new record each time a ping is performed every ten minutes, the structure of the table can be found below.

I am looking to calculate the uptime percentage of each server as there is a OFFLINE or ONLINE string inserted depending on the server's reach-ability. Admittedly I'm not great at advanced SQL like this, my limit is pretty much joins!

+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| server_id   | bigint unsigned | NO   |     | NULL    |                |
| status      | varchar(255)    | NO   |     | NULL    |                |
| map_name    | varchar(255)    | YES  |     | NULL    |                |
| num_players | int             | YES  |     | NULL    |                |
| max_players | int             | YES  |     | NULL    |                |
| created_at  | timestamp       | YES  |     | NULL    |                |
| updated_at  | timestamp       | YES  |     | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Any advice on how to do this would be fantastic, I've been racking my brains for a while over this.

Infinity
  • 13
  • 3
  • Please add which dbms are you using, some data examples and expected result. The `ping`, `percentage` and `uptime` are not needed – Ergest Basha Apr 02 '22 at 18:59

1 Answers1

1

If you want the percentage of all connections use:

SELECT
  (100 * COUNT(status)) /
  COUNT(CASE WHEN status = 'ONLINE' THEN 1
  ELSE 0 END AS percentage_uptime
FROM server_connections;

You will probably want to be more specific and group by

  • date or time
  • server_id or map_name For example, depending on the rdbms you might use:
SELECT 
  server_id,
  DAY(created_at) AS "Day",
  (100 * COUNT(status)) /
  COUNT(CASE WHEN status = 'ONLINE' THEN 1
  ELSE 0 END AS percentage_uptime
FROM server_connections
GROUP BY
server_id,
DAY(created_at);

which will give you daily figures for each server.