OK - 2nd try forgot the WHERE msg.status < 2 If you don't include the msg.status < 2, then you will be counting all messages, whether deleted or not. status=2 deleted (expunge done), status=3 deleted waiting for purge run to actually be deleted.
Well, If you are looking for the used space of the user in general, then dbmail_users.curmail_size is the column you want. SELECT curmail_size FROM dbmail_users WHERE userid="theuser"; But I think you want to get a per mailbox (folder) look, so: SELECT users.userid user, mbx.name mailbox, COUNT(msg.message_idnr) message_count, SUM(phys.messagesize) size FROM dbmail_mailboxes mbx JOIN dbmail_users users ON (mbx.owner_idnr=users.user_idnr) JOIN dbmail_messages msg ON (mbx.mailbox_idnr=msg.mailbox_idnr) JOIN dbmail_physmessage phys ON (phys.id=msg.physmessage_id) WHERE msg.status < 2 GROUP BY mbx.mailbox_idnr ORDER BY users.userid, mbx.name; That will give you all users, all mailboxes, order by user then mailbox. If you want just a paticular userid and maybe just one mailbox of theirs, then add to the where clause AND users.userid="theuser" AND users.userid="theuser" AND mbx.name="INBOX" -Jon Simon wrote: > Hi There, > > We have a external client management app that we would like to be able > to calculate the size of a dbmail mailbox with a SQL query. Can anyone > give me some pointers here to get this right please? > > Thanks > > Simon > > -- > Scanned for viruses and dangerous content by *MailScanner* > <http://www.mailscanner.info/> > ------------------------------------------------------------------------ > > _______________________________________________ > DBmail mailing list > [email protected] > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail > -- Scanned for viruses and dangerous content by MailScanner _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
