Group by wrong results?

From: Daniel Fink <"Daniel>
Date: Tue, 7 Aug 2018 10:26:28 -0600
Message-ID: <CAL2aWL4PpdSueJDO6GHdhdxwOcLa_epjpNnLdYWt_NNnnW1exw_at_mail.gmail.com>

A script to extract session count from ash data is not returning the right results using a TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') group by unless I use a factored subquery.

Query 1
WITH session_count AS
( SELECT sample_time, count(1) sess_count
  FROM dba_hist_active_sess_history
  WHERE sample_time >= TRUNC(sysdate - (1/24))   GROUP BY sample_time
)
SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,

       MAX(sess_count) max_sessions
FROM session_count
GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') ORDER BY sample_minute
/
Wrong Results example

SAMPLE_MINUTE SESS_COUNT

• ----------

20180727 09:09 5

20180727 09:09 1

20180727 09:09 1

20180727 09:09 5

20180727 09:09 2

20180727 09:09 4

20180727 09:10 3

20180727 09:10 5

20180727 09:10 1

20180727 09:10 4

20180727 09:10 7

20180727 09:10 9

If I add another factored subquery, I get the right aggregation

WITH session_count AS

( SELECT sample_time, count(1) sess_count

  FROM dba_hist_active_sess_history

  WHERE sample_time >= sysdate - (1/24)

  GROUP BY sample_time

),

session_minutes

AS

( SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,

      sess_count

FROM session_count

)

SELECT sample_minute,

       MAX(sess_count)

FROM session_minutes

GROUP BY sample_minute

ORDER BY sample_minute

Correct results

SAMPLE_MINUTE MAX(SESS_COUNT)

• ---------------

20180727 09:01 22

20180727 09:02 22

20180727 09:03 15

20180727 09:04 10

20180727 09:05 11

20180727 09:06 10

20180727 09:07 20

20180727 09:08 20

20180727 09:09 5

20180727 09:10 9

20180727 09:11 12

20180727 09:12 12

20180727 09:13 9

20180727 09:14 6

20180727 09:15 3

Why is the first query (which looks correct to me) not properly aggregating?

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 07 2018 - 18:26:28 CEST