MySQL Forums
Forum List  »  Newbie

Re: SQL Output
Posted by: Vino B
Date: April 13, 2019 08: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

Options: ReplyQuote


Subject
Written By
Posted
April 05, 2019 01:27AM
April 05, 2019 10:10AM
April 12, 2019 06:43AM
April 12, 2019 09:15AM
Re: SQL Output
April 13, 2019 08:42AM
April 13, 2019 10:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.