DDL was posted in wrong thread, here it is.
DROP TABLE IF EXISTS `run_log`;
CREATE TABLE `run_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host_pattern` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `status_log`;
CREATE TABLE `status_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`host_id` int(11) NOT NULL,
`task_id` int(11) NOT NULL,
`ok` tinyint(1) NOT NULL DEFAULT '1',
`unreachable` tinyint(1) DEFAULT '0',
`skipped` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `host_id` (`host_id`),
KEY `fk_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into status_log
As you stated the requirement, you do not want totals grouped by host or task, just grand totals. so your Group By and join to run_log are unnecessary. The columns are not-null and the boolean results are mutually exclusive so the total is the count(*). So ...
select
round(100*ok/n,2) as ok_pct,
round(100*unreachable/n,2) as unreachable_pct,
round(100*skipped/n,2) as skipped_pct,
n
from (
select
sum(ok) as ok,
sum(unreachable) as unreachable,
sum(skipped) as skipped,
count(*) as n
from status_log
) sums;
Edited 1 time(s). Last edit at 04/13/2019 10:47AM by Peter Brawley.