/* global */
SELECT
	COUNT(*) AS 'Total number of commands',
	SUM(user_time) / 50.0		AS 'Total user time (sec.)',
	SUM(system_time) / 50.0		AS 'Total system time (sec.)',
	SUM(effective_time) / 50.0	AS 'Total effective time (sec.)'
FROM log_accounting_process \G

/* by command count */
SELECT
	command AS 'Command',
	COUNT(*) AS 'Number of commands',
	SUM(user_time) / 50.0		AS 'Total user time (sec.)',
	SUM(system_time) / 50.0		AS 'Total system time (sec.)',
	SUM(effective_time) / 50.0	AS 'Total effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
	command AS 'Command',
	COUNT(*) AS 'Number of commands',
	SUM(user_time) / 50.0		AS 'Total user time (sec.)',
	MIN(user_time) / 50.0		AS 'Minimal user time (sec.)',
	AVG(user_time) / 50.0		AS 'Average user time (sec.)',
	MAX(user_time) / 50.0		AS 'Maximal user time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
	command AS 'Command',
	COUNT(*) AS 'Number of commands',
	SUM(system_time) / 50.0		AS 'Total system time (sec.)',
	MIN(system_time) / 50.0		AS 'Minimal system time (sec.)',
	AVG(system_time) / 50.0		AS 'Average system time (sec.)',
	MAX(system_time) / 50.0		AS 'Maximal system time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
	command AS 'Command',
	COUNT(*) AS 'Number of commands',
	SUM(effective_time) / 50.0		AS 'Total effective time (sec.)',
	MIN(effective_time) / 50.0		AS 'Minimal effective time (sec.)',
	AVG(effective_time) / 50.0		AS 'Average effective time (sec.)',
	MAX(effective_time) / 50.0		AS 'Maximal effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;

--
-- User stats:
--

/* by command count */
SELECT
	p.username AS 'Username',
	COUNT(*) AS 'Number of commands',
	SUM(user_time) / 50.0		AS 'Total user time (sec.)',
	SUM(system_time) / 50.0		AS 'Total system time (sec.)',
	SUM(effective_time) / 50.0	AS 'Total effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
	p.username AS 'Username',
	COUNT(*) AS 'Number of commands',
	SUM(user_time) / 50.0		AS 'Total user time (sec.)',
	MIN(user_time) / 50.0		AS 'Minimal user time (sec.)',
	AVG(user_time) / 50.0		AS 'Average user time (sec.)',
	MAX(user_time) / 50.0		AS 'Maximal user time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
	p.username AS 'Username',
	COUNT(*) AS 'Number of commands',
	SUM(system_time) / 50.0		AS 'Total system time (sec.)',
	MIN(system_time) / 50.0		AS 'Minimal system time (sec.)',
	AVG(system_time) / 50.0		AS 'Average system time (sec.)',
	MAX(system_time) / 50.0		AS 'Maximal system time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
	p.username AS 'Username',
	COUNT(*) AS 'Number of commands',
	SUM(effective_time) / 50.0		AS 'Total effective time (sec.)',
	MIN(effective_time) / 50.0		AS 'Minimal effective time (sec.)',
	AVG(effective_time) / 50.0		AS 'Average effective time (sec.)',
	MAX(effective_time) / 50.0		AS 'Maximal effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;