Versions Compared

Old Version 1

changes.mady.by.user Ana Teixeira

Saved on

compared with

New Version Current

changes.mady.by.user Luis Sousa

Saved on

Key

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

...

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;