# Support to Extract a Report using Data Explorer Plugin

**URL:** <https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040>\
**Category:** Product Support\
**Tags:** data-explorer, sql-query\
**Created:** [November 24, 2024, 10:57am UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040 "2024-11-24T10:57:08Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![Eman\_Ahmed](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/eman_ahmed/32/542047_2.png) [@Eman\_Ahmed](https://meta.discourse.org/u/Eman_Ahmed)\
**Post date:** [November 24, 2024, 10:57am UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/1 "2024-11-24T10:57:08Z")

</div>

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.

```sql
-- 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:

 ![image](https://global.discourse-cdn.com/meta/original/4X/d/7/7/d77c750be1a8e10c95b456bb1a0e59717ee99d1d.png)

---

_[View the full topic](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040)._