SQL & PL/SQL

In an earlier post Frank Kulash helped me by putting together this query, so if Frank is available to help me again, i would be grateful.

What i was wanting to see if it would be possible to add a line to display what the percent would be by dividing the wait time by the count??

WITH	rng	AS
(	-- Begin sub-query rng, to compute rng_num, cnt
SELECT	wait_time
,	COUNT (*)	AS cnt
,	MAX	(
		CASE
			WHEN wait_time <= 10	THEN	0
			WHEN wait_time <= 30	THEN	11
			WHEN wait_time <= 60	THEN	31
						ELSE	61
		END
		)	AS rng_num
	FROM	motor_assist2
--	where((:p53_fiscal_yr IS NULL
--	OR :p53_fiscal_yr = TO_CHAR (ADD_MONTHS (datetime, 6), 'YYYY'))
--	OR (:p53_month IS NULL OR :P53_month = TO_CHAR(DATETIME,'MM')
	GROUP BY	wait_time
)	-- End sub-query rng, to compute rng_num, cnt
--
--
,	rnk	AS
(	-- Begin sub_query rnk, to compute rnk_num, txt
	SELECT	rng.*
	,	ROW_NUMBER () OVER
		(	PARTITION BY	rng_num
			ORDER BY	cnt		DESC
			,		wait_time
		)			AS rnk_num
	,	TO_CHAR (wait_time)
		|| '(Min):Count:'
		|| TO_CHAR (cnt)	AS txt
	FROM	rng
)	-- End sub_query rnk, to compute rnk_num, txt
--
--
,	all_rnk	AS
(	-- Begin sub-query all_rnk, list of all possible rnk_nums
	SELECT DISTINCT	rnk_num
	FROM	rnk
)	-- End sub-query all_rnk, list of all possible rnk_nums
SELECT	CASE
		WHEN  GROUPING (a.rnk_num) = 0
		THEN  TO_CHAR (a.rnk_num)
		ELSE  'Total:'
	END	AS rnk_txt
,	CASE
		WHEN  GROUPING (a.rnk_num) = 0
		THEN	MAX (CASE WHEN rng_num = 0  THEN txt END)
		ELSE  'Count: ' || TO_CHAR	(
			SUM (CASE WHEN rng_num = 0  THEN cnt END)
						)
	END	AS "0-10MIN"
,	CASE
		WHEN  GROUPING (a.rnk_num) = 0
		THEN	MAX (CASE WHEN rng_num = 11  THEN txt END)
		ELSE  'Count: ' || TO_CHAR	(
			SUM (CASE WHEN rng_num = 11  THEN cnt END)
						)
	END	AS "11-30MIN"
,	CASE
		WHEN  GROUPING (a.rnk_num) = 0
		THEN	MAX (CASE WHEN rng_num = 31  THEN txt END)
		ELSE  'Count: ' || TO_CHAR	(
			SUM (CASE WHEN rng_num = 31  THEN cnt END)
						)
	END	AS "31-60MIN"
,	CASE
		WHEN  GROUPING (a.rnk_num) = 0
		THEN	MAX (CASE WHEN rng_num = 61  THEN txt END)
		ELSE  'Count: ' || TO_CHAR	(
			SUM (CASE WHEN rng_num = 61  THEN cnt END)
						)
	END	AS "61+MIN"
FROM		all_rnk	a
LEFT OUTER JOIN	rnk	r  ON a.rnk_num	= r.rnk_num
GROUP BY	ROLLUP (a.rnk_num)
ORDER BY	a.rnk_num
;

Thanks
Deanna

Edited by: Dclipse03 on Jan 23, 2009 8:59 AM