SQL & PL/SQL

Hi,

I had a requirement like to show the data by each group wise count like "<100" "100-199" "200 - 299" " 300 - 399"400-499 500-599 600-699 700-799 800-899 900-999 >=1000

With the below query able to get the count in between the range and total . But unable to get the Min and Max values for that range. For example if the count <100 is 3 then in those 3 the lowest value is need to show in the Min column. Same for Max also.

Required the Median value also on those values.

Thanks in advance.



Requirement is like below :
State <100 100-199 200-299 300-399 400-499 500-599 600-699 700-799 800-899 900-999 >=1000 Total Min Max Median
AK 1 2 0 4 1 4 4 35 35 4 1 25 $85 $1,200 $850
AL 0 0 2 27 10 17 35 2 2 35 0 103 $100 $1,500 $750


*"QUERY "*

WITH t AS
(SELECT 'AL' state, 12 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 67 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 23 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 12 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 12 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 78 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 34 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 4 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 12 VALUE FROM DUAL
UNION ALL
SELECT 'AL' state, 15 VALUE FROM DUAL
UNION ALL
SELECT 'AZ' state, 6 VALUE FROM DUAL
UNION ALL
SELECT 'AZ' state, 123 VALUE FROM DUAL
UNION ALL
SELECT 'AZ' state, 123 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 23 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 120 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 456 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 11 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 24 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 34 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 87 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 23 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 234 VALUE FROM DUAL
UNION ALL
SELECT 'MA' state, 789 VALUE FROM DUAL
UNION ALL
SELECT 'MH' state, 54321 VALUE FROM DUAL),
-- End of test data
t1 AS
( SELECT state,
NVL (COUNT (DECODE (VALUE, 0, 0)), 0) "<100",
NVL (COUNT (DECODE (VALUE, 1, 1)), 0) "100-199",
NVL (COUNT (DECODE (VALUE, 2, 2)), 0) "200-299",
NVL (COUNT (DECODE (VALUE, 3, 3)), 0) "300-399",
NVL (COUNT (DECODE (VALUE, 4, 4)), 0) "400-499",
NVL (COUNT (DECODE (VALUE, 5, 5)), 0) "500-599",
NVL (COUNT (DECODE (VALUE, 6, 6)), 0) "600-699",
NVL (COUNT (DECODE (VALUE, 7, 7)), 0) "700-799",
NVL (COUNT (DECODE (VALUE, 8, 8)), 0) "800-899",
NVL (COUNT (DECODE (VALUE, 9, 9)), 0) "900-999",
NVL (COUNT (DECODE (VALUE, 10, 10)), 0) ">=1000"
FROM (SELECT state,
CASE
WHEN VALUE < 100 THEN 0
WHEN VALUE BETWEEN 100 AND 199 THEN 1
WHEN VALUE BETWEEN 200 AND 299 THEN 2
WHEN VALUE BETWEEN 300 AND 399 THEN 3
WHEN VALUE BETWEEN 400 AND 499 THEN 4
WHEN VALUE BETWEEN 500 AND 599 THEN 5
WHEN VALUE BETWEEN 600 AND 699 THEN 6
WHEN VALUE BETWEEN 700 AND 799 THEN 7
WHEN VALUE BETWEEN 800 AND 899 THEN 8
WHEN VALUE BETWEEN 900 AND 999 THEN 9
WHEN VALUE >= 1000 THEN 10
END
VALUE
FROM t)
GROUP BY state)
SELECT STATE,
"<100",
"100-199",
"200-299",
"300-399",
"400-499",
"500-599",
"600-699",
"700-799",
"800-899",
"900-999",
">=1000",
"<100"
+ "100-199"
+ "200-299"
+ "300-399"
+ "400-499"
+ "500-599"
+ "600-699"
+ "700-799"
+ "800-899"
+ "900-999"
+ ">=1000"
total,
least("<100",
"100-199",
"200-299",
"300-399",
"400-499",
"500-599",
"600-699",
"700-799",
"800-899",
"900-999",
">=1000") min_val,
greatest("<100",
"100-199",
"200-299",
"300-399",
"400-499",
"500-599",
"600-699",
"700-799",
"800-899",
"900-999",
">=1000") max_val
FROM t1
/