How can I select data size group by id for every value in a table

Question

I hope I can explain exactly what I need to get.

I have a Table that has a specific column of type MEDIUMBLOB. What I need to do is to determine the size of every value inserted on this column grouped by idMsg and transform this value in MB.

DB Structure is:

CREATE TABLE `sat_msgBlob` (
    `idOwnApp` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `idMsg` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `idVeh` MEDIUMINT(11) NULL DEFAULT NULL,
    `idTypeMsg` MEDIUMINT(11) NULL DEFAULT NULL,
    `idProviderComm` MEDIUMINT(11) NULL DEFAULT NULL,
    `idPayCode` MEDIUMINT(11) NULL DEFAULT NULL,
    `msgInOut` CHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `idMsgSender` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `txMsg` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `tmMsg` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `idControl` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `txBlob` MEDIUMBLOB NULL DEFAULT NULL,
    `idUpdUser` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmUpdate` DATETIME NULL DEFAULT NULL,
    `idMission` MEDIUMINT(11) NULL DEFAULT NULL,
    `idEvent` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmPrcsLast` DATETIME NULL DEFAULT NULL,
    `idBlob` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`idMsg`, `tmMsg`) USING BTREE,
    INDEX `X2` (`idVeh`, `idOwnApp`, `tmMsg`) USING BTREE,
    INDEX `m1` (`idMission`, `idEvent`) USING BTREE,
    INDEX `X3` (`idControl`) USING BTREE,
    INDEX `X4` (`tmMsg`) USING BTREE,
    INDEX `X6` (`idBlob`) USING BTREE,
    INDEX `X7` (`tmUpdate`) USING BTREE
)
COLLATE='utf8_general_ci'
AUTO_INCREMENT=971935070
AVG_ROW_LENGTH=8450
MAX_ROWS=4294967295;

The data inserted in this txBlob column look like this

0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C21444F435459504520566D4D4C2053595354454D2022687474703A2F2F7777772E766961747261636B2E726F2F737065632F566D4D4C5F76302E312E6474

I had tried to use this query but I am not sure that the value is correct:

SELECT sys.format_bytes(LENGTH(txBlob)) AS size 
FROM sat_msgBlob 
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02' 
GROUP BY idMsg

Answer 1

It seems that you need in

SELECT idMsg, SUM(LENGTH(txBlob)) AS total_size_in_bytes
FROM sat_msgBlob 
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02' 
GROUP BY idMsg

Then, after receiving the output into the client you may format it to Mbytes. Or divide the sum in the query to 1048576 and cast to decimal with needed fractional part length.

Answer 2

I think I found the right query:

SELECT idMsg, CONCAT( ROUND( SUM(OCTET_LENGTH(txBlob)/1024), 2 ), ' KB' ) AS KB_size, tmMsg FROM sat_msgBlob WHERE tmMsg BETWEEN '2021-03-16' AND '2021-03-17' GROUP BY idMsg
ORDER BY KB_size

Thank you!