Combine all of the 1's, 2's, 3's etc.... 1

[gshadz37]

Hello, in the query I have below it results in multiple 1's, 2's, 3's, 4's, and 5's in the Client Score column:

SELECT
ROUND (TT.CLIENT_SCORE,0) AS CLIENTSCORE,
ROUND (TT.WORK_TIME/360,2) AS MTTR,
COUNT(*) AS TOTAL


FROM

ARADMIN.TTTICKETINFO TT
WHERE

(ARADMIN.CONVERT_REMEDY_DATE(TT.CREATE_DATE)) between TO_DATE('04/24/06','MM/DD/YY HH24:MI')
and TO_DATE('04/25/06','MM/DD/YY HH24:MI')
AND (TT.TRANSPORT_METHOD LIKE ('T1%'))
AND (TT.MTTR_REPORTABLE IN ('1'))
AND (TT.SERVICE_OUT_INDICATOR IN ('1'))

GROUP BY
TT.CLIENT_SCORE,
TT.WORK_TIME


What I need it to to is show a '1' row with an average of the MTTR column, then a count in the Total column, then a '2'row with an average of the MTTR column, then a count in the Total column, and so on. Does anyone know of a way to achieve this?

Thank you.

Thank you.

 

[SantaMufasa]

qshadz,

Your problem is that you are grouping by TT.CLIENT_SCORE, but I infer by your "ROUND (TT.CLIENT_SCORE,0)" expression that TT.CLIENT_SCORE is not an integer value, thus your GROUP BY TT.CLIENT_SCORE will produce many more groupings (of non-integer groups) than you really want. To resolve this, I recommend:

...GROUP BY
ROUND (TT.CLIENT_SCORE,0),
ROUND (TT.WORK_TIME/360,2)

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gshadz37]

I tried, I get the same results:

CLIENTSCORE MTTR TOTAL
1.00 5.61 1.00
2.00 36.33 1.00
3.00 1.82 1.00
3.00 3.68 1.00
3.00 6.73 1.00
3.00 22.13 1.00
3.00 42.18 1.00

 

[SantaMufasa]

gshadz,

That is because you get one row of output every time that the combination of "ROUND (TT.CLIENT_SCORE,0) and ROUND (TT.WORK_TIME/360,2)" changes. since "1.82" differs from "3.68" and so on, you will see a distinct row for each distinct combination.

Since you are willing to have an AVERAGE of the MTTRs, then that sould resolve the problem. Try this code:

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

Let us know if this produces the results you need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gshadz37]

Yes, that did it! THANK YOU!