SELECT SA1.CLIENTID,COUNT(1) AS CLIENTSESSIONS
    --,(SELECT COUNT(1) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID AND SENTDATASIZE>0) AS OUTGOINGNBSESSIONS
      , SUM(CASE WHEN SENTDATASIZE=0 THEN 1 ELSE 0 END) AS OUTGOINGNBSESSIONS
    --,(SELECT TRUNC(SUM(SENTDATASIZE)/1048576,2)  FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID) AS OUTGOINGDATASIZE
      , TRUNC(SUM(SENTDATASIZE)/1048576,2) AS OUTGOINGDATASIZE
    --(SELECT COUNT(1) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID AND RECEIVEDDATASIZE>0) AS INCOMINGNBSESSIONS,
      , SUM(CASE WHEN RECEIVEDDATASIZE>0 THEN 1 ELSE 0 END) AS INCOMINGNBSESSIONS
    --(SELECT TRUNC(SUM(RECEIVEDDATASIZE)/1048576,2) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID) AS INCOMINGDATASIZE,
      , TRUNC(SUM(RECEIVEDDATASIZE)/1048576,2) AS INCOMINGDATASIZE
    --,(SELECT COUNT(1) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID AND RECEIVEDDATASIZE=0 AND SENTDATASIZE=0 ) AS NODATASESSIONS
      , SUM(CASE WHEN RECEIVEDDATASIZE=0 AND SENTDATASIZE=0 THEN 1 ELSE 0 END) AS NODATASESSIONS
    --(SELECT TRUNC(AVG(SA2.AVGRESPONSETIME)/1000,2) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID and avgresponsetime<>0) AS AVGRESPONSETIME,
      , TRUNC(AVG(CASE WHEN avgresponsetime<>0 THEN AVGRESPONSETIME ELSE NULL END)/1000,2) AS AVGRESPONSETIME
    --(SELECT TRUNC(AVG(SA2.DBACCESSTIME/NUMBEROFMESSAGES)/1000,2) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID and numberofmessages<>0) AS DB_ACCESS_TIME,
      , TRUNC(AVG(CASE WHEN numberofmessages <> 0 THEN DBACCESSTIME/NUMBEROFMESSAGES ELSE NULL END)/1000,2) AS DB_ACCESS_TIME
    --(SELECT TRUNC(AVG(SA2.DBACCESSTIME/(SA2.AVGRESPONSETIME*SA2.NUMBEROFMESSAGES)*100),2) FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID and numberofmessages<>0 and avgresponsetime<>0) AS DB_TIME_PCT,
      , TRUNC(AVG(CASE WHEN numberofmessages<>0 AND avgresponsetime<>0 THEN DBACCESSTIME/(AVGRESPONSETIME*NUMBEROFMESSAGES) ELSE NULL END)*100,2) AS DB_TIME_PCT
    --(SELECT TRUNC(MAX(SA2.STARTTIMESTAMP), 'DDD') FROM SESSIONACTIVITY SA2 WHERE SA2.CLIENTID=SA1.CLIENTID) AS CALENDARDAY
      , TRUNC(MAX(STARTTIMESTAMP), 'DDD') AS CALENDARDAY
FROM SESSIONACTIVITY SA1
WHERE numberofmessages<>0
GROUP BY SA1.CLIENTID
ORDER BY COUNT(1) DESC;