ORACLE统计属于一个用户名的SID的个数，并检索该SID的每个会话所使用的cpu值的总和

Question

我试图拉属于每个特定用户名的sid计数和cpuusedbysession的总和。在下面的方式，但它不允许我分组。谁能指出我哪里错了?

SELECT ss.username,
         COUNT (DISTINCT se.SID) AS "Count of SID",
         VALUE / 100 AS "TOTAL_CPU"
    FROM gv$session ss, gv$sesstat se, gv$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
GROUP BY username;

Answer 1

由于您是按用户名分组，因此在不使用任何聚合函数(如sum,count,max等)的情况下，您不能出现除用户名以外的列。也许你应该试试这个:

SELECT ss.username,
         COUNT (DISTINCT se.SID) AS "Count of SID",
         SUM(VALUE) / 100 AS "TOTAL_CPU"
    FROM gv$session ss, gv$sesstat se, gv$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
GROUP BY username;