Document toolboxDocument toolbox

Finding Attachment Statistic in Jira

MySQL / PostgreSQL 

-- Find Attachment Statistic
SELECT p.pkey, p.pname, EXTRACT(YEAR FROM f.created) yr, COUNT(f.id) AS "# of files", MIN(f.filesize)/1024 AS "min size (kb)", 
AVG(f.filesize)/1024 AS "average size(kb)", MAX(f.filesize)/1024 AS "max size(kb)", SUM(f.filesize)/(1024*1024) AS "total size(mb)" 
FROM fileattachment f, project p, jiraissue i 
WHERE f.issueid = i.id AND i.project = p.id 
GROUP BY p.pkey, p.pname, yr;

Sample Result