SQL Learning Center - GoldMine

DescriptionThis query is for dBase hosted GoldMine. It returns counts by user for deleted emails that need to be emptied from deleted folder.

SELECT
 mb.userid
,mb.folder
,count(*)
FROM
 mailbox mb
WHERE
 mb.folder not in
( 'X-GM-INBOX'
,'Sent'
,'Filed'
,'X-GM-DRAFTS'
,'X-GM-FOLDERS'
,'X-GM-GROUPS'
,'X-GM-HTMLTAB'
,'X-GM-ICALINFO'
,'X-GM-OUTBOX'
,'X-GM-PROP-HTMLTAB'
,'X-GM-RULES'
,'X-GM-TEMPLATES'
,'X-GM-SMIME-CA'
,'X-GM-SUBSENT'
,'X-GM-SUBFILED'
,'X-GM-TD-ITEMS'
,'X-GM-WEBIMPORT'
)
GROUP BY
 mb.userid
,mb.folder
ORDER BY
 mb.userid
,mb.folder