Group mail table records into categories of 100,000 bytes
mysql> mysql> mysql> mysql> CREATE TABLE mail -> ( -> t DATETIME, # when message was sent -> senderUser CHAR(8), # sender (source user and host) -> senderHost CHAR(20), -> recipientUser CHAR(8), # recipient (destination user and host) -> recipientHost CHAR(20), -> size BIGINT, # message size in bytes -> INDEX (t) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size) -> VALUES -> ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274), -> ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925), -> ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048), -> ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271), -> ('2010-05-14 09:31:37','venus','gene','mars','barb',2291), -> ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781), -> ('2010-05-14 14:42:21','venus','barb','venus','barb',98151), -> ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482), -> ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824), -> ('2010-05-15 08:50:57','venus','phil','venus','phil',978), -> ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532), -> ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856), -> ('2010-05-16 09:00:28','venus','gene','mars','barb',613), -> ('2010-05-16 23:04:19','venus','phil','venus','barb',10294), -> ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873), -> ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992) -> ; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT FLOOR((size+99999)/100000) AS 'size (100KB)', -> COUNT(*) AS 'number of messages' -> FROM mail GROUP BY 1; +--------------+--------------------+ | size (100KB) | number of messages | +--------------+--------------------+ | 1 | 13 | | 2 | 1 | | 10 | 1 | | 24 | 1 | +--------------+--------------------+ 4 rows in set (0.00 sec) mysql> mysql> drop table mail; Query OK, 0 rows affected (0.00 sec)