HOW TO USE ORACLE GROUP BY?

Question

Im try to execute this select sentence with a group by:

SELECT 'WMSALPRO2' AS SERVER,
       0 AS "TOTALSESIONES"
FROM   DUAL
UNION ALL
SELECT 'WMSALPRO1' AS SERVER,
       0 AS "TOTALSESIONES"
FROM   DUAL
UNION ALL 
SELECT SERVER AS SERVER,
       COUNT(*) AS "TOTALSESIONES" 
FROM   (
  SELECT b.sid,
         b.username,
         b.module,
         TRUNC(a.seconds_in_wait/60,2) AS MINUTOS_ESPERA,
         a.event,
         c.object_name,
         b.sql_id,
         d.instance_name AS SERVER 
  FROM   gv$session_wait a,
         gv$session b,
         dba_objects c,
         gv$instance d 
  WHERE  a.event NOT IN (
           'Streams AQ: waiting for messages in the queue',
           'smon timer',
           'pmon timer',
           'rdbms ipc message',
           'SQL*Net message from client',
           'pipe get',
           'null event',
           'SQL*Net message to client'
         ) 
  AND    b.SID = a.SID 
  AND    d.inst_id=a.inst_id 
  AND    username IS NOT NULL 
  AND    C.OBJECT_ID(+) = row_wait_obj# 
  AND    B.STATUS ='ACTIVE'
)
GROUP BY SERVER; 

But in the result don´t group by the name

SERVER           TOTALSESIONES
---------------- -------------
WMSALPRO2                    0
WMSALPRO1                    0
WMSALPRO2                    4
WMSALPRO1                    8

Any idea?

Answer 1

You can write the query a little simpler (and using ANSI joins rather than the legacy comma joins) as:

SELECT COALESE(must_include.server, actual.server) AS server,
       COALESE(actual.TOTALSESIONES, 0) AS TOTALSESIONES
FROM   (
  SELECT 'WMSALPRO2' AS SERVER FROM DUAL
  UNION ALL
  SELECT 'WMSALPRO1' FROM DUAL
) must_include
FULL OUTER JOIN (
  SELECT d.instance_name AS SERVER,
         COUNT(*) AS TOTALSESIONES
  FROM   gv$session_wait a
         INNER JOIN gv$session b
         ON (b.SID = a.SID)
         INNER JOIN gv$instance d
         ON (d.inst_id=a.inst_id)
         RIGHT OUTER JOIN dba_objects c
         ON (C.OBJECT_ID = row_wait_obj#)
  WHERE  a.event NOT IN (
           'Streams AQ: waiting for messages in the queue',
           'smon timer',
           'pmon timer',
           'rdbms ipc message',
           'SQL*Net message from client',
           'pipe get',
           'null event',
           'SQL*Net message to client'
         ) 
  AND    username IS NOT NULL 
  AND    B.STATUS ='ACTIVE'
  GROUP BY SERVER
) actual
ON (must_include.SERVER = actual.SERVER);

Answer 2

Because Group by is outside of union selects.

IF you include all statements and group them, it will be okay:

    select sub.SERVER, sum(*) from (
SELECT 'WMSALPRO2' AS SERVER, 0 AS "TOTALSESIONES"
  FROM DUAL
UNION ALL
SELECT 'WMSALPRO1' AS SERVER, 0 AS "TOTALSESIONES"
  FROM DUAL
UNION ALL
SELECT SERVER AS SERVER, COUNT(*) AS "TOTALSESIONES"
  FROM (SELECT b.sid,
               b.username,
               b.module,
               TRUNC(a.seconds_in_wait / 60, 2) AS MINUTOS_ESPERA,
               a.event,
               c.object_name,
               b.sql_id,
               d.instance_name AS SERVER
          FROM gv$session_wait a, gv$session b, dba_objects c, gv$instance d
         WHERE a.event NOT IN ('Streams AQ: waiting for messages in the queue',
                               'smon timer',
                               'pmon timer',
                               'rdbms ipc message',
                               'SQL*Net message from client',
                               'pipe get',
                               'null event',
                               'SQL*Net message to client')
           AND b.SID = a.SID
           AND d.inst_id = a.inst_id
           AND username IS NOT NULL
           AND C.OBJECT_ID(+) = row_wait_obj#
           AND B.STATUS = 'ACTIVE')
 GROUP BY SERVER) sub 
 group by SERVER

Note: I didnt get the logic about putting these statements inside the query, they are 0 anyways :

SELECT 'WMSALPRO2' AS SERVER, 0 AS "TOTALSESIONES"
  FROM DUAL
UNION ALL
SELECT 'WMSALPRO1' AS SERVER, 0 AS "TOTALSESIONES"
  FROM DUAL