MySQL Forums
Forum List  »  Newbie

Re: Count and Outptu
Posted by: Peter Brawley
Date: April 13, 2019 10:46AM

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.

Options: ReplyQuote


Subject
Written By
Posted
April 11, 2019 10:11PM
April 12, 2019 09:23AM
April 12, 2019 09:50AM
April 12, 2019 03:45PM
April 13, 2019 10:42AM
Re: Count and Outptu
April 13, 2019 10:46AM
April 16, 2019 02:25AM
April 16, 2019 07:39PM


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.