Versions Compared

Old Version 3

changes.mady.by.user Luis Sousa

Saved on

compared with

New Version 4

changes.mady.by.user Luis Sousa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


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:

Code Block
languagesql
SELECT
    COUNT(*) AS num_attachments,
    AVG(ao.FILE_SIZE / 1000000) AS avg_filesize_Mb,
    SUM(ao.FILE_SIZE / 1000000) 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:

Code Block
languagesql
SELECT 
	COUNT(*) AS num_attachments,
	SUM(file_size / (1024 * 1024)) AS total_size_MB
	FROM AO_8B1069_ATTACHMENT;


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


Code Block
languagesql
SELECT
    ao.FILE_NAME,
    ao.FILE_SIZE / 1000000 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;