Dear All,
I need your support to execute an SQL code to view the sizes of each category to follow up periodically to ensure that the category is not exceeding the agreed size by the moderators.
We executed the following code but the result is displayed in bytes and we need it to be displayed in GB or MB.
– Query to estimate the data size for each category in MB and GB
SELECT
c.id AS category_id,
c.name AS category_name,
COUNT(DISTINCT t.id) AS topics_count,
COUNT(p.id) AS posts_count,
ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024, 2) AS total_text_size_mb,
ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024 / 1024, 4) AS total_text_size_gb,
ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024, 2) AS total_uploads_size_mb,
ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024 / 1024, 4) AS total_uploads_size_gb,
ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024, 2) AS total_category_size_mb,
ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024 / 1024, 4) AS total_category_size_gb
FROM
categories c
LEFT JOIN
topics t ON t.category_id = c.id
LEFT JOIN
posts p ON p.topic_id = t.id
LEFT JOIN
post_custom_fields pcf ON pcf.post_id = p.id AND pcf.name = ‘uploads’
LEFT JOIN
uploads u ON u.id = pcf.value::int
WHERE
c.id > 0 – Exclude system categories
AND t.deleted_at IS NULL – Exclude deleted topics
AND p.deleted_at IS NULL – Exclude deleted posts
GROUP BY
c.id, c.name
ORDER BY
total_category_size_mb DESC
Need your support to display it in the following format:
