Re: Count and Outptu
Posted by:
Vino B
Date: April 13, 2019 10:42AM
Hi Peter,
Here you go,
select sum(a.ok = 1) / t.total * 100 as Successful,
sum(a.ok = 0) / t.total * 100 as Failed,
sum(a.unreachable = 1) / t.total * 100 as Unreachable,
sum(a.skipped = 1) / t.total * 100 as Skipped from status_log as a,
(select sum(ok = 1) + sum(ok = 0) + sum(unreachable = 1) + sum(skipped = 1) as total from status_log,
run_log where host_id = run_log.id and run_log.host_pattern = 'Dev') as t group by t.total;
DROP TABLE IF EXISTS `status_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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`),
CONSTRAINT `fk_task` FOREIGN KEY (`task_id`) REFERENCES `task_log` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `run_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;
From,
Vino.B