SELECT ARCHIVE_FLAG, count(1) as CNT FROM ARCHIVE_INFO GROUP BY ARCHIVE_FLAG ORDER BY ARCHIVE_FLAG

result:

ARCHIVE_FLAG	CNT
-1	310
0	83910
1	912571
2	4198

select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT

from MBX_MESSAGE mm
group by to_char(mm.CREATED_DATETIME, 'YYYY')
order by to_char(mm.CREATED_DATETIME, 'YYYY')

result:

YEAR1	CNT
2018	9943
2019	14530
2020	265567
2021	4198

Messages on mailboxes by created date

select mm.MESSAGE_ID,mm.CREATED_DATETIME,mm.MESSAGE_SIZE,mm.MAILBOX_ID,mb.PATH,mm.DOC_ID
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
order by mm.CREATED_DATETIME

Messages on mailboxes group by year with count and sum

select to_char(mm.CREATED_DATETIME, 'YYYY') as ANO, mb.PATH as mbx, count(1) as CNT, SUM(mm.MESSAGE_SIZE) as SUM1
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
group by to_char(mm.CREATED_DATETIME, 'YYYY'), mb.PATH

Ver também