Hi Lester.

I cannot answer your question, but wonder what your PLANs are -
particularly for the subselects. And does it make any difference if you
extend your use of use of aliases, i.e.

SELECT T.ROOM
, COUNT( T.* ) AS COUNTTOTAL
, MAX( T.PREVIOUS ) AS MAXIMUM
// Less than x
, ( SELECT COUNT (T1.*) FROM TRANSACTIONS T1 //Added aliases in all subselects
WHERE T1.TICKET_REF BETWEEN :FROMD AND :TOD
AND T1.APPLET = T.APPLET AND T1.ROOM = T.ROOM
AND T1.PREVIOUS < ( SELECT R1.X FROM ROOMSTAT R1 WHERE R1.TAG =
T.ROOM )
)
AS CNT1
// Between x and y
, ( SELECT COUNT (T2.*) FROM TRANSACTIONS T2
WHERE T2.TICKET_REF BETWEEN :FROMD AND :TOD
AND T2.APPLET = T.APPLET AND T2.ROOM = T.ROOM
AND T2.PREVIOUS >= ( SELECT R2.X FROM ROOMSTAT R2 WHERE R2.TAG =
T.ROOM )
AND T2.PREVIOUS < ( SELECT R3.Y FROM ROOMSTAT R3 WHERE R3.TAG =
T.ROOM )
)
AS CNT2
// Greater than y
, ( SELECT COUNT (*) FROM TRANSACTIONS T3
WHERE T3.TICKET_REF BETWEEN :FROMD AND :TOD
AND T3.APPLET = T.APPLET AND T3.ROOM = T.ROOM
AND T3.PREVIOUS >= ( SELECT R4.X FROM ROOMSTAT R4 WHERE R4.TAG =
T.ROOM )
//This is probably a copying mistake? AND PREVIOUS < ( SELECT Y FROM
ROOMSTAT WHERE TAG = T.ROOM )
)
AS CNT3
, T.APPLET
FROM TRANSACTIONS T
WHERE T.TICKET_REF BETWEEN :FROMD AND :TOD //and an alias here as well
(shouldn't matter)
GROUP BY T.APPLET DESC, T.ROOM
ORDER BY T.APPLET, T.ROOM

Roomstat.tag is something you want an index on (of course), and probably
also a compound index on (transactions.room, transactions.applet,
transactions.ticket_ref - important to have transaction.ticket_ref behind
the other fields of this index). I don't quite understand why you use >=
when saying it is greater than (you only use < for less than), but the
logic behind everything is all yours.

HTH,
Set