Sometimes, when talking about Xray attachments, it can be helpful to understand what is stored in Xray datatables and their size. 


With this simple SQL query, is possible to know the size of attachments by project:

SELECT
    COUNT(*) AS num_attachments,
    AVG(CAST(ao.FILE_SIZE AS FLOAT) / 1048576) AS avg_filesize_Mb,
    SUM(CAST(ao.FILE_SIZE AS FLOAT) / 1048576) AS total_filesize_Mb,
    p.pname,
    p.pkey
FROM
    project p
JOIN
    jiraissue j ON p.id = j.project
JOIN
    AO_8B1069_ATTACHMENT ao ON ao.ISSUE_ID = j.id
GROUP BY
    p.pkey,
    p.pname;


SQL for all projects:

SELECT
  COUNT(*) AS num_attachments,
  SUM(CAST(file_size AS FLOAT) / (1024 * 1024)) AS total_size_MB
  FROM AO_8B1069_ATTACHMENT;


SQL to know the size of each individual attachment by project:


SELECT
    ao.FILE_NAME,
    CAST(ao.FILE_SIZE AS FLOAT) / 1048576 AS filesize_Mb,
    p.pname,
    p.pkey
FROM
    project p
JOIN
    jiraissue j ON p.id = j.project
JOIN
    AO_8B1069_ATTACHMENT ao ON ao.ISSUE_ID = j.id;