# 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:** 2

<div class="post-metadata">

**Author:** ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)\
**Post date:** [November 24, 2024, 6:25pm UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/2 "2024-11-24T18:25:31Z")

</div>

I think you just need to change the calcs to reflect your units. for example, for GB

```sql
-- Query to estimate the data size for each category in 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 / 1024, 4) AS total_text_size_gb,
    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 / 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_gb DESC

```

---

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