Morning All,
I'm trying to Rank Username based on the Total Sum of amount waived but I want to avoid Ranking the Overall Total at the bottom, plus I dont want them in Ranking order, I want the order to stay the same as it currently is.
SELECT DECODE(GROUPING(USERNAME),1,'TOTAL',0,UPPER(USERNAME)) as "USERNAME",
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JAN' THEN AMOUNT_WAIVED ELSE 0 END) AS JAN,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'FEB' THEN AMOUNT_WAIVED ELSE 0 END) AS FEB,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'MAR' THEN AMOUNT_WAIVED ELSE 0 END) AS MAR,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'APR' THEN AMOUNT_WAIVED ELSE 0 END) AS APR,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'MAY' THEN AMOUNT_WAIVED ELSE 0 END) AS MAY,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JUN' THEN AMOUNT_WAIVED ELSE 0 END) AS JUN,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JUL' THEN AMOUNT_WAIVED ELSE 0 END) AS JUL,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'AUG' THEN AMOUNT_WAIVED ELSE 0 END) AS AUG,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'SEP' THEN AMOUNT_WAIVED ELSE 0 END) AS SEP,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'OCT' THEN AMOUNT_WAIVED ELSE 0 END) AS OCT,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'NOV' THEN AMOUNT_WAIVED ELSE 0 END) AS NOV,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'DEC' THEN AMOUNT_WAIVED ELSE 0 END) AS DEC,
SUM(AMOUNT_WAIVED) AS "TOTAL"
FROM A3_FACT_AMS_WAIVED_ARREARS
WHERE TO_CHAR(DATE_PROCESSED,'YYYY')='2012'
GROUP BY ROLLUP (USERNAME)
USERNAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL
AARKOVITS 0 6.5 39 0 39 0 32.5 6.5 6.5 0 0 0 130
ABINGE 52 0 0 19.5 19.5 39 78 234 0 0 0 0 442
ACOUGHLIN 0 0 9.75 6.5 39 21.68 0 26 6.5 0 0 0 109.43
BFOSTER 39 0 26 0 0 0 19.5 6.5 0 0 0 0 91
BWOOLLARD 0 0 0 0 6.5 0 0 0 0 0 0 0 6.5
CCHAPMAN 0 0 0 0 0 260 13 19.5 39 0 0 0 331.5
CLAW 0 13 0 0 0 0 0 0 0 0 0 0 13
CROBSON 0 32.5 0 0 0 0 0 0 0 0 0 0 32.5
EATMORE 71.5 13 26 39 0 0 13 0 0 0 0 0 162.5
EMORRIS 0 0 0 0 0 13 97.5 13 0 0 0 0 123.5
EPENNINGTON 0 0 0 0 0 0 13 13 0 0 0 0 26
EROLLASON 0 0 19.5 52 0 0 0 0 0 0 0 0 71.5
EWATKINSON 0 19.5 13 32.5 0 0 0 0 0 0 0 0 65
FSEYMOUR 32.5 100 19.5 0 61.4 0 0 0 0 0 0 0 213.4
GTYSON 0 32.5 0 91.75 6.5 0 0 149.5 0 0 0 0 280.25
HMACKENZIE 0 0 0 39 0 0 0 19.5 0 0 0 0 58.5
HTILFORD 0 0 0 19.5 13 19.5 0 71.5 0 0 0 0 123.5
IRYDER 0 0 0 0 0 0 0 50.87 74.64 0 0 0 125.51
JASHTON 0 6.5 13 39 0 0 46.5 13 0 0 0 0 118
JCOOPER 39.1 97.5 40.5 147.78 26 0 6.5 26 0 0 0 0 383.38
JCOUGHLIN 0 0 13 0 0 0 13 0 0 0 0 0 26
JHUDSON 0 0 58.5 0 0 0 0 0 0 0 0 0 58.5
JLEARMONTH 0 45.5 73 32.5 78 45.5 0 0 0 0 0 0 274.5
JPARKER 0 0 6.5 39 6.5 0 0 0 0 0 0 0 52
JPOULTER 0 0 0 0 0 58.5 0 0 0 0 0 0 58.5
JRUTHERFORD 0 0 0 0 0 0 26 0 0 0 0 0 26
JWILSON 0 0 13 26 0 0 6.5 0 0 0 0 0 45.5
KCLEMISHAW 0 0 0 6.5 6.5 0 0 0 0 0 0 0 13
KCOOK 0 0 0 13 0 0 13 0 0 0 0 0 26
KSMITH 0 0 0 0 0 0 0 6.5 0 0 0 0 6.5
KSYKES 0 0 13 0 0 0 0 0 13 0 0 0 26
LMUSGROVE 0 0 0 0 0 0 40.5 0 0 0 0 0 40.5
LPRINGLE 0 0 6.5 0 0 6.5 0 26 0 0 0 0 39
LSANFORD-KARLSSON 0 0 0 19.5 0 45.5 6.5 0 0 0 0 0 71.5
LUNWIN 0 0 0 0 0 39 0 6.5 0 0 0 0 45.5
MBELL 0 0 0 0 0 6.5 0 0 0 0 0 0 6.5
MBELLERBY 0 6.5 0 1.43 0 0 0 0 0 0 0 0 7.93
MFOUNTAIN 56.5 31.5 2.46 1.25 149.5 0 0 0 0 0 0 0 241.21
MJONES 0 0 0 123.5 0 0 0 0 0 0 0 0 123.5
MWILSON 0 0 0 0 13 0 0 0 0 0 0 0 13
NDEIGHTON1 0 78.18 78.08 0 0 78 143 78 0 0 0 0 455.26
NLDEIGHTON 0 0 0 0 0 0 0 21 0 0 0 0 21
NLIMBERT 117 45.5 0 0 0 0 0 130 0 0 0 0 292.5
OFRANKSPEDRICK 0 0 0 0 32.5 0 0 0 0 0 0 0 32.5
PDUFFY 0 130 149.5 162.5 0 227.5 240.5 175.5 26 0 0 0 1111.5
RBELL 0 26 26 13 0 26 39 0 0 0 0 0 130
RBIRCH 0 0 0 13 208 0 0 0 0 0 0 0 221
RGLYNN 0 0 13 0 6.5 0 13 19.5 0 0 0 0 52
RSANDERS 0 18.42 19.5 0 6.5 27.5 0 45.5 0 0 0 0 117.42
RTHOMPSON 84.17 108.06 152.5 41.17 84.5 51.58 54.16 63.94 0 0 0 0 640.08
SANDREWS 19.5 0 0 0 26 39 0 0 0 0 0 0 84.5
SDEMAINE 0 0 26 0 0 0 0 0 0 0 0 0 26
SDOWSON 0 6.5 0 0 6.5 0 26.06 0 0 0 0 0 39.06
SHARRIS 0 6.5 0 0 0 0 0 0 0 0 0 0 6.5
SMARSHALL 39 0 0 0 0 0 6.5 0 0 0 0 0 45.5
SSIMPSON 0 0 0 0 0 19.5 0 0 0 0 0 0 19.5
STEER 0 13 19.5 13 13 19.5 0 0 0 0 0 0 78
STOMLINSON 0 0 45.5 43.5 0 39 13 0 30.5 0 0 0 171.5
STYERS 0 0 0 0 0 0 0 6.5 0 0 0 0 6.5
TSOLLITT 0 84.5 12.5 19.5 39 0 78 52 0 0 0 0 285.5
WTASSELLI 0 0 65 0 0 0 39 91 0 0 0 0 195
ZDUTOIT 0 13 0 0 26 1.5 19.5 0 0 0 0 0 60
TOTAL 550.27 934.16 999.29 1054.88 912.9 1083.76 1096.72 1370.81 196.14 0 0 0 8198.93