GROUP BY a number range

[gshadz37]

I am looking for a way to Group By a neumber range. For example, each row will have a number range in the first column:
0-100
101-200
201-300

...and so on. Here is the query in its current form:

SELECT 
	   (ROUND (TT.TICKET_SCORE,0)) AS TICKETSCORE,
       avg(ROUND (TT.WORK_TIME/3600,2)) AS MTTR,
       COUNT(*) AS TOTAL
	       
  FROM 
  	   ARADMIN.TTTICKETINFO TT
	       
 	WHERE 
 	   		(ARADMIN.CONVERT_REMEDY_DATE(TT.CREATE_DATE))
        	between TO_DATE('03/1/06','MM/DD/YY HH24:MI')    
            and TO_DATE('04/1/06','MM/DD/YY HH24:MI')
   			AND (TT.TRANSPORT_METHOD LIKE ('%DSL%'))
			AND (TT.MTTR_REPORTABLE IN ('1'))
   			AND (TT.SERVICE_OUT_INDICATOR IN ('1'))
 GROUP BY 
  	   	  	ROUND (TT.TICKET_SCORE,0);

which gives me a list of each ticket score. I'm not sure how to group in the ranges mentioned above. Hopefully I explained this clearly.

 

[LKBrwnDBA]

Maybe?:

SELECT...
   FROM...
  GROUP BY TRUNC((ROUND(TT.TICKET_SCORE,0)-1)/100);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[gshadz37]

Not exactly the results I'm looking for:

TICKETSCORE MTTR TOTAL
1.00 1.54 63.00
2.00 4.20 470.00
3.00 16.68 73.00
4.00 34.32 23.00

I need to actually show the range under ticket score if possible -
0-100
101-200
etc...

 

[ddiamond]

The following should work:

SELECT 
  DECODE(
    LEAST(ROUND(TT.TICKET_SCORE,0),100),ROUND(TT.TICKET_SCORE,0),
    '0-100',
    TO_CHAR(ROUND(TT.TICKET_SCORE-51,-2)+1) || '-' 
    || TO_CHAR(ROUND(TT.TICKET_SCORE+49,-2))) AS RANGE,
  avg(ROUND (TT.WORK_TIME/3600,2)) AS MTTR,
  COUNT(*) AS TOTAL
FROM 
  ARADMIN.TTTICKETINFO TT
WHERE 
      (ARADMIN.CONVERT_REMEDY_DATE(TT.CREATE_DATE))
  between TO_DATE('03/1/06','MM/DD/YY HH24:MI')    
  and TO_DATE('04/1/06','MM/DD/YY HH24:MI')
     AND (TT.TRANSPORT_METHOD LIKE ('%DSL%'))
  AND (TT.MTTR_REPORTABLE IN ('1'))
     AND (TT.SERVICE_OUT_INDICATOR IN ('1'))
GROUP BY 
  DECODE(
    LEAST(ROUND(TT.TICKET_SCORE,0),100),ROUND(TT.TICKET_SCORE,0),
    '0-100',
    TO_CHAR(ROUND(TT.TICKET_SCORE-51,-2)+1) || '-' 
    || TO_CHAR(ROUND(TT.TICKET_SCORE+49,-2))) AS RANGE

I was not able to find logic that worked for both the range 0-100 and the other ranges. Hence the decode function. If anyone can think of a way to do it without a decode, please post.